SUM instead of grand total.

[EugeneKreger]

How do I get sum of %'s in total column instead of grand total? See the example below.

Update: from an Excel perspective it is row total (sum), basically.

[pi_eye · Accepted solution]

Hi @EugeneKreger 

You can create the totals you need by aggregating an iteration (ie summing the percent calculation)

The results above are calculated using 2 measures.

This is the main measure:

percent total row = SUMX(
    DISTINCT(clusteredData[Status]), 
    [percent of age group]
)

Sum iterates around each distinct status and calculates a lists of percents which it then adds together. The trick is that in each cell of the table, there is only one status! So the correct percentage is returned.

Below is the measure that we are iterating on for the %:

percent of age group = sum(clusteredData[Value])/CALCULATE(sum(clusteredData[Value]),ALLSELECTED(clusteredData[Age Group]))

HTH

Pi

[pi_eye · Accepted solution]

Hi @EugeneKreger 

You can create the totals you need by aggregating an iteration (ie summing the percent calculation)

The results above are calculated using 2 measures.

This is the main measure:

percent total row = SUMX(
    DISTINCT(clusteredData[Status]), 
    [percent of age group]
)

Sum iterates around each distinct status and calculates a lists of percents which it then adds together. The trick is that in each cell of the table, there is only one status! So the correct percentage is returned.

Below is the measure that we are iterating on for the %:

percent of age group = sum(clusteredData[Value])/CALCULATE(sum(clusteredData[Value]),ALLSELECTED(clusteredData[Age Group]))

HTH

Pi

[EugeneKreger]

Hi @pi_eye 

Thanks for the solution, it seems to work as expected, although I started seeing infinity results where I had blanks previously. How do I turn them into nulls?

[EugeneKreger · Accepted solution]

Seems like IFERROR(value, BLANK()) construct works very well here.

[Greg_Deckler]

@EugeneKreger First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8