Forum Discussion

Largefinger's avatar
Largefinger
Frequent Visitor
4 years ago
Solved

Calculating Measures Totals after if statement

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

Need Help

10 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Largefinger
      Frequent Visitor
      4 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        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's avatar
    Largefinger
    Frequent Visitor
    4 years ago

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