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

Helper V

## Measure Total problems

Hello All,
Struggling with a totals issue in BI. Per the embedded image, I have a measure that calculates the cost of each resource on a project using our pro forma rates. This is working correctly. The problem is, I'm struggling to get it to calculate the sum of all of those resources for the project. Instead, what I get is the sum of the resource rates for resources x the sum of all of their hours. I've tried to filter the measure by Project but I'm definitely doing something wrong. any insight is appreciated.
Here is the measure:
Actual Cost Pro Forma rates =
// actual quantity * resource pro forma rate
CALCULATE(
SUMX('Systems Actuals with Teams',[Quantity])*SUMX('Resources',[Resource Standard Rate]))

Attached is a screenshot of the table generated and a view of the relationship between the involved tables
1 ACCEPTED SOLUTION
Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User

Try Like

``````SUMX(Summarize('Systems Actuals with Teams','Systems Actuals with Teams'[Project Name]
,"_1",[Quantity],"_2",SUMX('Resources',[Resource Standard Rate])),[_1]*[_2])``````

Row context need to set

Helper V

Hi @amitchandak ,

I appreciate the quick response.  I am trying to use the DAX you provided but running into Syntax issues.  Where you have [Quantity] after the _1, DAX will not allow me to use the Quantity column as specified.

Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper V

Thanks so much for that link.  That seems to work out perfectly.

Pro Forma Project Actuals Total =
Var _table =SUMMARIZE('Resources',[Resource Name],"_value",[Actual Cost Pro Forma rates])
return
IF(HASONEVALUE('Resources'[Resource Name]),'Systems Actuals with Teams'[Actual Cost Pro Forma rates],SUMX(_table,[_Value]))
Super User
It's a classic! 🙂

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.