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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.