% of Grand Total by Individual Report

[Anonymous]

I am building a tool in Power BI that is taking multiple, individual reports and aggregating them into one giant report so that I can measure certain metrics across all of the reports. (For extra clarity: the individual reports use the exact same columns. It's a recurring report and I'm wanting to track certain metrics over time)

Here's an example: Let's say you have a report that has four columns: Apples, Oranges, and Week of the Year.

Report 1:

State Apples Oranges Week of the Year

California	3	4	1
Texas	2	6	1
Oregon	4	3	1

Report 2:

State Apples Oranges Week of the Year

California	5	1	2
Texas	7	8	2
Oregon	6	5	2

What my report would be trying to do (in the above example) would be track the percentage of the grand total of apples sold within the different states by Week. (E.g. for California, Week 1 should show 25% (3 apples from California/12 total apples) and Week 2 would show 28% (5 apples from California/18 total apples)).

The issue is that I can't seem to figure out the way to get Power BI to show % of Grand Total by individual report. When I aggregate my reports and start putting them in visuals, I'm performing the Quick Calc to show % of Grand Total. What Power BI ends up showing is the % of the aggregated grand total. In my example above, this would be the equivalent of California reporting 27% of total apples sold (8 apples from California/30 total apples).

Is there a way that anyone knows of to make Power BI show % of Grand Total relative to individual reports? Or maybe of another way to get the information that I'm looking for?

[Phil_Seamark · Accepted solution]

Maybe add two calculated measures

Apples Sum of Week = CALCULATE(sum('Reports'[Apples]),ALLEXCEPT('Reports',Reports[Week of the Year])) 

Apples Sum of Week Percent = DIVIDE(sum('Reports'[Apples]),[Apples Sum of Week])

And format the 2nd measure as a percent.  Let me know how you get on.  If it works you can repeat for Oranges (just don't compare them (boom boom))

[Anonymous]

@Phil_Seamark

One last part to the question: if I wanted to add an additional dimension for Fruit Quality that is a child underneath the State dimension, how can I make the % of Total dynamically change to work with whatever level of dimension I'm working in?

For example (we'll just stick with Apples right now), see this pivot table:

What I would like to be able to do is dig down into the Fruit Quality dimension and see the total percentage of "Above Average" apples sold within California (1 Above Average apple/3 total apples from California in week 1, 3 Above Average apples/5 total apples from California in week 2, etc.)

What I'm currently seeing when I'm only looking at the State level is % of Apples, by State, by week (yay!); however, when I zoom in to look at a specific state, the numbers reported there seem to follow this formula: (Total Above Average Apples in California)/(Total Apples Sold Across All States). Does that make sense?

I appreciate any help and advice. Let me know if I need to reword something to make it make more sense.

[Anonymous]

You're a genius. Thank you so much!