March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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
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.
Sorry I missed read your response.
Unfortunately, it doesn't work as expected.
Share the download link of your PBI file.
I have sent the file in a private message. Thanks
Hi,
You may download my PBI file from here.
Hope this helps.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
157 | |
97 | |
79 | |
69 |