Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Largefinger
Frequent Visitor

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

 

Largefinger_0-1663195946374.png
Result: (first two columns are the same actuals and planned in the measure)
Largefinger_5-1663197378748.png

 


 

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)

Largefinger_2-1663196410085.pngLargefinger_3-1663196440132.png

 

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

 

Largefinger_4-1663196939642.png

 Thanks

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Largefinger
Frequent Visitor

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

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry I missed read your response. 

 

Unfortunately, it doesn't work as expected.

Largefinger_0-1663208015121.png

 

Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I have sent the file in a private message. Thanks 

Pbix file 

 

Hi,

You may download my PBI file from here.

Hope this helps.Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors