cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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``````

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

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
10 REPLIES 10
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])``````

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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
Frequent Visitor

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.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Unfortunately, it doesn't work as expected.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

I have sent the file in a private message. Thanks

Frequent Visitor
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### 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.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors