Measure Total problems

[Clint]

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

[amitchandak]

@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

[Clint]

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.

[Greg_Deckler · Accepted solution]

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

[Clint]

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]))

[Greg_Deckler]

It's a classic! 🙂