Percent of Total based on Dynamic Filters

[Anonymous]

Does anyone know if it's possible to have a dynamic % of Total metric in Power BI?

With some help from the community, I've managed to get a % of Total for the main dimension of my data; now, I'm just trying to make % of Total work when I dig deeper.

Here's an example to illustrate what I'm talking about: Let's say I'm tracking, by week, how many apples were grown from each state as well as the quality of those apples. The end goal would be to track the quality of the apples, by state, week by week.

Here's a pivot table for my hypothetical scenario:

What I would like to be able to do is dig down into the Fruit Quality dimension and see the percentage of total apples grown within California were "Above Average" (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 zoom in to look at a specific state (for example, California), the % of Total numbers reported there seem to follow this formula: (Total Above Average Apples in California)/(Total Apples Sold Across All States). So I end up with a really small % of Total because Power BI is comparing the small segment with the total data. Does that make sense?

If I wanted to accomplish this in a pivot table, I would simply go into the Value Field Settings for the Apples value and show values as % of Parent Row Total. Is there a way to do something similar in Power BI?

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

[jorgelopez141]

 

Hi All, 

Im trying to make each of the bars in the second bar chart (the one in the bottom) to return the sum of the values shown in the first bar chart (in this case, each of the bars in the second bar chart should show 2.23 ). 

Basically, the catch is that as you add filters to the page, both bar charts should update accordingly. 

Can anyone help?

Ps. How do you start a new thread?

[Sean]

@Anonymous

Try something like this... (you have not posted sample data so let me know if this doesn't work)

% of Parent Row Total Measure =
IF (
    ISFILTERED ( Table[Quality] ),
    DIVIDE (
        SUM ( Table[Value] ),
        CALCULATE ( SUM ( Table[Value] ), ALL ( Table[Quality] ) ),
        0
    ),
    DIVIDE (
        CALCULATE ( SUM ( Table[Value] ), ALL ( Table[Quality] ) ),
        CALCULATE ( SUM ( Table[Value] ), ALLSELECTED ( Table ) ),
        0
    )
)

If you already have a Measure you can substitute it for the underlined part.

God Luck!

[Anonymous]

@Sean unfortunately, that did not work. I'm still new to the forum, so can you tell me how to post the data that you're wanting as well as what that kind of data would be? I assumed the the pivot table along with details on what was going on in the pivot table were "sample data", but, again, I'm new to the forums 🙂

[Sean]

@Anonymous

1) On how to post look here

http://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

And yes post raw/unedited data meaning not the Pivot Table result but the underlying data source(s)!

2) Second Please verify in your Excel Pivot Table that you really need % of Parent Row Total

in Excel in the PivotTable Fields area (on the right side of the screen)

in the Values area - select your Measure - click the little triangle - Value Filed Settings - in the dialog-box select as in the picture

Now in the Pivot table do you get the result you want?

3) Yes also post the results you are expecting.

[Anonymous]

@Sean Thank you for the tutorial and your patience. Let's try this again!

I'll start by answering your second point about verifying whether I need this % of Parent Row Total. The answer is, "Yes, because I am trying to build a report in Power BI." This whole issue could be summed up as: I am trying to replicate the % of Parent Row Total functionality in a Power BI report.

Here is my raw (but scrubbed) data that I'm working with:

The end result that I'm shooting for is to track the amount of [Impressions] are associated with a particular [PostClickQualityScore] by week. It also needs to be able to be sliced with dimensions such as [Campaign] or [Label] so that I can track this data at a granular level.

Another way of saying the above is that I am needing to be able to look at the % of Total [Impressions] that fall into a given [PostClickQualityScore] value by week and within different dimensions.

At this time, I can see exactly what I want to see as long as I don't put any dimensions on.

The three lines pictured above sum to 100% (everything checks out); however, once I try and view the data through some kind of dimension (e.g. [Label] = Label 1), then I get something like this:

At the final data point, the two lines pictured above sum to 1.07%. This is where I turned to these forums. Is there a way to have the % of Total flex to display % of Total for whatever dimension I am currently looking in? Let me know if there's something else that would be helpful to see or needs to be further clarified.

[Sean]

@Anonymous

Can you copy the data from the excel grid (your 1st image) and paste it here - click Post twice (ignore error warnings)

[Anonymous]

Here you go:

Criteria	CampaignName	AdGroupName	Impressions	PostClickQualityScore	Label	Week	Year
Keyword	Campaign 1	Ad Group 1	41	Above average	Label 1	6	2017
Keyword	Campaign 2	Ad Group 2	49	Average	Label 2	6	2017
Keyword	Campaign 3	Ad Group 3	8	Above average	Label 1	6	2017
Keyword	Campaign 4	Ad Group 4	80	Above average	Label 4	6	2017
Keyword	Campaign 5	Ad Group 5	45	Above average	Label 5	6	2017
Keyword	Campaign 6	Ad Group 6	36	Above average	Label 6	6	2017
Keyword	Campaign 3	Ad Group 7	67	Above average	Label 1	6	2017
Keyword	Campaign 8	Ad Group 8	28	Above average	Label 4	6	2017
Keyword	Campaign 9	Ad Group 9	67	Above average	Label 4	6	2017
Keyword	Campaign 10	Ad Group 10	77	Above average	Label 4	6	2017
Keyword	Campaign 1	Ad Group 1	71	Above average	Label 1	5	2017
Keyword	Campaign 2	Ad Group 2	15	Average	Label 2	5	2017
Keyword	Campaign 3	Ad Group 3	84	Above average	Label 1	5	2017
Keyword	Campaign 4	Ad Group 4	66	Above average	Label 4	5	2017
Keyword	Campaign 5	Ad Group 5	4	Above average	Label 5	5	2017
Keyword	Campaign 6	Ad Group 6	21	Above average	Label 6	5	2017
Keyword	Campaign 3	Ad Group 7	40	Above average	Label 1	5	2017
Keyword	Campaign 8	Ad Group 8	8	Above average	Label 4	5	2017
Keyword	Campaign 9	Ad Group 9	89	Above average	Label 4	5	2017
Keyword	Campaign 10	Ad Group 10	32	Above average	Label 4	5	2017

[Sean · Accepted solution]

How about this?

% of Total Measure =
DIVIDE (
    SUM ( 'Table'[Impressions] ),
    CALCULATE (
        SUM ( 'Table'[Impressions] ),
        ALLSELECTED ( 'Table'[PostClickQualityScore] ),
        ALLEXCEPT ( 'Table', 'Table'[Week] )
    ),
    0
)

If this is also does not produce the derised result - build a pivot table (like the the 1 in your original post)

with the sample data but this time also show what the % actually should be.

[Rajiv1237]

DIVIDE(SUM(RegionQtyData[Qty]), CALCULATE(SUM(RegionQtyData[Qty]), ALLSELECTED(RegionQtyData[Item])))

[Rajiv1237]

Dynamic Percentage:

DIVIDE(SUM(RegionQtyData[Qty]), CALCULATE(SUM(RegionQtyData[Qty]), ALLSELECTED(RegionQtyData[Item])))

[Anonymous]

The code snippet you posted ended up being 98% of what I needed. I had to add a couple of columns to the ALLEXCEPT function, but it works now! Thank you for your tremendous amount of help and patience.

If you're interested, here was the final form of the code:

% of Total Measure = 
DIVIDE(
	SUM(LPE[Impressions]),
	CALCULATE(
		SUM(LPE[Impressions]),
		ALLSELECTED(LPE[Rating]),
		ALLEXCEPT(LPE,LPE[Year/Week],LPE[Label],LPE[Ad Group])
	),
	0
)