cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
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
relationship.PNGwrong total.PNG
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Measure Total problems

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


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: Measure Total problems

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


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IX
Super User IX

Re: Measure Total problems

@Clint 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper V
Helper V

Re: Measure Total problems

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.

Highlighted
Helper V
Helper V

Re: Measure Total problems

Hi @Greg_Deckler ,

 

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]))
Highlighted
Super User IX
Super User IX

Re: Measure Total problems

It's a classic! 🙂

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors