topic Re: Totals based on each row in DAX Commands and Tips

Totals based on each row

Sidhu — Mon, 03 Oct 2022 16:18:25 GMT

I need to calcualte the total opportunity from average for each category, somehow the totals are not showing up, is there any way to display the totals, Here is the data and the dax I have used.

Data

Country	Location	Spend	net
USA	Category1	100	500
USA	Category2	20	100
UK	Category1	50	400
UK	Category2	40	100

DAX -

Measure - SpendPerNet = DIVIDE(SUM('Table'[Spend]),SUM('Table'[Net]),0)*1000

Measure - OpportunityfromAverage = VAR SpendPerNetAverage = CALCULATE(AVERAGEX(VALUES('Table'[Location Category]),[SpendPerNet]),ALL('Table'[Location]))

RETURN IF([SpendPerNet]-SpendPerNetAverage>0,('Table'[SpendPerNet]-SpendPerNetAverage)*sum('Table'[Net])/1000,0)

Opportunity Average total should sum both 18.75 and 10 and show as 28.75. I tried multiple options but nothing worked.

Re: Totals based on each row

Greg_Deckler — Mon, 03 Oct 2022 16:19:52 GMT

Sidhu 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

Re: Totals based on each row

Sidhu — Mon, 03 Oct 2022 17:26:14 GMT

Greg_Deckler Thanks for the quick reply, I am getting a different result now. Is this because how I calculate average?

Re: Totals based on each row

Greg_Deckler — Mon, 03 Oct 2022 17:58:32 GMT

Sidhu So the technique is to get your measure that works for your individual rows. Create a new measure like:

OpportunityfromAverageTotal = VAR __Table = SUMMARIZE('Table',[Location],[Category],"__Measure",[OpportunityfromAverage]) RETURN IF(HASONEVALUE('Table'[Country]),[OpportunityfromAverage],SUMX(__Table,[Measure]))

Re: Totals based on each row

Sidhu — Mon, 03 Oct 2022 18:04:26 GMT

Greg_Deckler I tried that but the totals are blank

Re: Totals based on each row

Greg_Deckler — Mon, 03 Oct 2022 18:26:38 GMT

Sidhu What is [Location Category], I would have expected [Location], [Category]

Re: Totals based on each row

Sidhu — Mon, 03 Oct 2022 18:38:22 GMT

Greg_Deckler Its a concatenated field of Location and category which should not have any impact, replaced it with location and category. Here is the link for the PBI file.

https://1drv.ms/u/s!AvujJeYUZlNThPRak2vASNT87nAssg?e=weT0gg

Re: Totals based on each row

Greg_Deckler — Mon, 03 Oct 2022 19:10:14 GMT

Sidhu OK, I got this to work (below). I did some refactoring so check the PBIX below signature.

OpportunityfromAverageTotal = VAR __Table1 = ADDCOLUMNS( SUMMARIZE(ALL('Table'),[Location],[Category]), "__SpendPerNet",[SpendPerNet], "__SpendPerNetAverage",[SpendPerNetAverage], "__Measure",[OpportunityfromAverage] ) RETURN IF(ISINSCOPE('Table'[Location]),[OpportunityfromAverage],SUMX(__Table1,[__Measure]))

Re: Totals based on each row

Sidhu — Mon, 03 Oct 2022 19:49:53 GMT

Greg_Deckler That worked, thank you so much