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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.