Calculating Measures Totals after if statement

[Largefinger]

Hi All,

I need some help with this dax measure. 

I am passing data through an if statement and getting the correct result at the row level, however, I can not work out how to calculate the total from the rows. 

I understand that I need to use the HASONEVALUE to replace the total, just not sure what with. 

Measure:

Spatial Conf - Tons achieved in planned location = 

var actual = CALCULATE(SUM('Actuals -Webbar'[MetricValue]),'Actuals -Webbar'[MetricSubtotalCode] = "Prod Ore To ROM"||'Actuals -Webbar'[MetricSubtotalCode] = "Dev Ore To ROM") 
var planned =CALCULATE(SUM('Plan 1'[bogged_tonnes]),'Plan 1'[CLASS] ="STOPE"|| 'Plan 1'[CLASS] ="DEV",'Plan 1'[Is above cut off grade]=1) 
var If_statement = 
                if(planned>0,
                    if(actual>=planned,
                        planned,
                        actual)
                   )

var Conformance_table = SUMMARIZE(Locations, (Locations[LocationCode]),"Conformance",If_statement)
var Result = if (HASONEVALUE(Locations[LocationCode]),sumx(Conformance_table,[Conformance]), If_statement)

return Result

Result: (first two columns are the same actuals and planned in the measure)

The Total in this case should be 4900 not the 13,125.

Using the coulmn chart I get the correct answer if I apply a location to the legend, but not with out (as below)

 My data model is  date and location tables, conneted to seperate plan and actuals tables 

 Thanks

[Largefinger]

Legend... thanks heaps for the assistance, that one was doing my head in👍

Just to complete the post, the solution was to break the measure down into an if statement and then use a sumx in another measure.

SC - Condition - Tons achieved in planned location = 
var actual = [Total Ore Hauled to ROM Actual (t)]
var planned =[P1 Total Ore Hauled to ROM Planned]

return if([planned]>0,if([actual]>=[planned],[planned],[actual]))

SC - Tons achieved in planned location = 
SUMX(VALUES(Locations[LocationCode]),'Actuals -Webbar'[SC - Condition - Tons achieved in planned location])

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Does this measure work?

Spatial Conf - Tons achieved in planned location = 

var actual = CALCULATE(SUM('Actuals -Webbar'[MetricValue]),'Actuals -Webbar'[MetricSubtotalCode] = "Prod Ore To ROM"||'Actuals -Webbar'[MetricSubtotalCode] = "Dev Ore To ROM") 
var planned =CALCULATE(SUM('Plan 1'[bogged_tonnes]),'Plan 1'[CLASS] ="STOPE"|| 'Plan 1'[CLASS] ="DEV",'Plan 1'[Is above cut off grade]=1) 
var If_statement = 
                if(planned>0,
                    if(actual>=planned,
                        planned,
                        actual)
                   )
var Result = sumx(VALUES(Locations[LocationCode]),If_statement)

return Result

[Largefinger]

Yes, It does. The results are in the table above and chart above; I cannot get the total right, though. 

The actual and planned variables are calculated out in different measures; I just thought I would show the details in case there was something that I was missing with a sumx vs sum.

[Ashish_Mathur]

I am confused about your response.  Does my measure work or not?  If it does not, then share the link from where i can download your PBI file.

[Largefinger]

Sorry I missed read your response. 

Unfortunately, it doesn't work as expected.

[Ashish_Mathur]

Share the download link of your PBI file.

[Largefinger]

I have sent the file in a private message. Thanks 

[Largefinger]

Pbix file 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.