Pivot Table Measure based on Column Values

Hello,

I am sure this is going to be a lot easier than I am making it out to be, but I just cannot figure out how to do what I would like.

I have the following Pivot Table (added to data model)

PivotTable.jpg

I would like to add a measure which replicates the percentage shown to the right of the pivot table but cannot figure out how to do it.

The add Calculated Field option is grey out as the source has been added to the Data Model, I just do not know how to reference the column values for each vehicle when creating a Measure. (if a measure is the "thing" I need to be creating)

Any and all help is greatly appreciated


[EDIT] I have now added an example file to the post.

[Update] Issue, solved by item #6 in the list.

Regards
Mike

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

See the attached. Right click on the row value for personal and select % of Row

Excel 2016 (Windows) 32 bit

	H	I	J	K	L
1		Classification
2		Business		Personal
3	Vehicle	Sum of Distance	Distance (Miles)	Sum of Distance	Distance (Miles)
4	ABC123	305	70.44%	128	29.56%
5	DEF456	200	51.41%	189	48.59%
6	GHI789	224	54.37%	188	45.63%
7	Grand Total	729	59.08%	505	40.92%

Sheet: Example

Try the following measures:
Business:=CALCULATE(SUM(T_Data[Distance]),T_Data[Classification]="Business")
Personal:=CALCULATE(SUM(T_Data[Distance]),T_Data[Classification]="Personal")
Personal %:=[Personal]/([Business]+[Personal])
Note that you may format Personal % as percentage with no decimal places in the data model window.
In the pivot table put Vehicle in the Rows area and then Business, Personal and Personal % in the Values area (Classification and Distance are not used).
Let us know if you have any questions.

Thanks, @JeteMc, spot-on reply, the perfect solution and generic enough that I can use this technique in other situations.

It has re-introduced me to the CALCULATE function.

Right, now how do I mark the question as Solved?

Many Thanks
Mike

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.