Totals based on each row

[Sidhu]

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.

[Greg_Deckler]

@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

[Sidhu]

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

[Greg_Deckler]

@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]))

[Sidhu]

@Greg_Deckler  I tried that but the totals are blank

[Greg_Deckler]

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

[Sidhu]

@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 

[Greg_Deckler · Accepted solution]

@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]))

[Sidhu]

@Greg_Deckler That worked, thank you so much