How to create measure using a specific column value as the denominator

[davebourgeois87]

Hello,

I'm still pretty new to Power BI and I'm having a ton of trouble understanding filter functions in DAX. Example:

The above table is an example where I want to create a measure "% of HH's", which I have done successfully. However, I want to then index the values to the total line (Total Panel in example above). I tried creating a measure that returns the Total Panel value, but I can't use that as a denominator for the other values in the column because of filtering. What I want to happen is something like the below from Excel:

Here is my current calculation: 

% HH Index = var denom = CALCULATE([Demo % of Total HH's],'Sheet1'[DEMOS]="Total Panel")

return [Demo % of Total HH's]/denom*100

Any help is greatly appreciated!

[ribisht17 · Accepted solution]

Hello,

Are you looking for something like this,

Note: Divide is the name of the sheet that I have

Please use this calculation to achieve the same

Solution 2 = SUM('Divide'[% of Total HH's]) / CALCULATE(MAX('Divide'[% of Total HH's]),ALL('Divide'),'Divide'[DEMOS]="Total Panel",'Divide'[PROD]=MAX('Divide'[PROD]))

Regards,

Ritesh,

Mark my post as a solution if it helped you| Gentlemen & Ladies|Munde and Kudis| I like your Kudos!! !!
My You Tube Channel !! Connect on Linkedin || Power BI for Tableau Users 

[davebourgeois87]

@ribisht17 Yes, this is great, thanks so much!

[ribisht17]

Please share your data-set, it is difficult to solve like this

How do I know about this Sheet1'[DEMOS]="Total Panel") ?

Regards,

Ritesh

[davebourgeois87]

Hi @ribisht17 

Here is a relevant data set:

PROD	DEMOS	% of Total HH's
DD	Total Panel	84.20%
DD	Gen Z (18-29) with kids under 6	85.80%
DD	Millennial (30-39) with kids under 6	80.60%
DD	Gen X (40-54) with kids under 6	71.90%
DD Brand 1	Total Panel	33.50%
DD Brand 1	Gen Z (18-29) with kids under 6	37.00%
DD Brand 1	Millennial (30-39) with kids under 6	33.00%
DD Brand 1	Gen X (40-54) with kids under 6	26.00%
DD Brand 2	Total Panel	28.90%
DD Brand 2	Gen Z (18-29) with kids under 6	35.70%
DD Brand 2	Millennial (30-39) with kids under 6	33.80%
DD Brand 2	Gen X (40-54) with kids under 6	31.50%
DD Brand 3	Total Panel	50.00%
DD Brand 3	Gen Z (18-29) with kids under 6	54.60%
DD Brand 3	Millennial (30-39) with kids under 6	47.70%
DD Brand 3	Gen X (40-54) with kids under 6	39.20%

My apologies, but I don't think I can share the actual file because it is on my organization's Sharepoint and is restricted. The data above is all that is needed though, I think. Essentially I want the calculation to happen separately for each product, but I want to divide all DEMOS except for Total Panel into DEMOS = Total Panel. So for PROD = DD, Gen Z/Total Panel, Millennial/Total Panel, Gen X/Total Panel, then the same for DD Brand 1. 

The result would look like this:

I really appreciate your help! Let me know if my data table is enough. 

[ribisht17]

Create a FIXED denominator, use ALL here  Solved: Create a fixed denominator - Microsoft Power BI Community

Measure 2 = DIVIDE([Count of UserID],CALCULATE([Count of UserID],ALL('warehouse OrderDetails'[Week End])))

Regards,

Ritesh

[davebourgeois87]

Hi @ribisht17 

Thank you for your quick response. I tried taking your suggestion, but it either does not work in this case or I did not apply the concept correctly. It ends up calculating within the demo instead of taking Gen Z % of HH's and dividing by Total Panel % of HH's:

Do you see anything stand out that I did incorrectly?

Thank you.