Calculated field based on rollup of other fields.

[Chillytouch]

Hi,

Sorry for the vague title, i'm newer to powerbi and I'm having a slight issue with a matrix that I've created. I had a request to create a matrix that shows revenue, salary, and then a calculation of those 2 all in one report with a trending view. The calculated field is just dividing one by the other. It's salary divded by revenue and then that's the percentage I'm showing.

Here's my issue: The data itself is at the fiscal week level. At that level, the calculated percentage is fine because there's no aggregation. When I roll up the report, it is rolling it up by the average of "paypercentrev". I don't want that though, I want the rollup of "PayPercentRev" to be  (Rollup value of Salary / Rollup value of Revenue). Does that make sense? Below is a screen shot of the report and you can see that the total for PayPercentRev is 9.9% which is incorrect, that is the average of weeks 1-4. I need it to be 9.2% which is 6,923 / 75,320. 

In a separate report I made this is much easier because it is a table where all 3 calculations are on one record. However to get the report to show like this I had to make a table that basically is at a "Measure Level". So in the table there is a row per fiscal week, per metric type (revenue/salary/paypercentrev), but they aren't all on the same record which is what makes it difficult. Below is the DAX I am using in the matrix: 

_MetricCustomP = 
VAR DisplayColumn = SELECTEDVALUE(MeasureSelect[MeasureName])
VAR Revenue = CALCULATE(SUM(vw_RevenueMeasure[metric_value]),FILTER(vw_revenuemeasure,vw_revenuemeasure[metric_type] = "Revenue"))
VAR Salary = CALCULATE(SUM(vw_RevenueMeasure[metric_value]),FILTER(vw_revenuemeasure,vw_revenuemeasure[metric_type] = "Salary"))
VAR PayPercentRev = IF(CALCULATE(AVERAGEX(vw_RevenueMeasure,vw_revenuemeasure[metric_value]),FILTER(vw_RevenueMeasure,vw_RevenueMeasure[metric_type] = "PayPercentRev" && vw_RevenueMeasure[metric_value]<>0 && vw_RevenueMeasure[metric_value]<>BLANK()))=0,BLANK(),CALCULATE(AVERAGEX(vw_RevenueMeasure,vw_RevenueMeasure[metric_value]),FILTER(vw_RevenueMeasure,vw_RevenueMeasure[metric_type] = "PayPercentRev" && vw_RevenueMeasure[metric_value]<>0 && vw_RevenueMeasure[metric_value]<>BLANK())))
RETURN
SWITCH(TRUE(), 
DisplayColumn = "Revenue", FORMAT(Revenue,"$0,0"),
DisplayColumn = "Salary", FORMAT(Salary,"$0,0"),
DisplayColumn = "PayPercentRev", FORMAT(PayPercentRev,"0.0%"))

Any advice is appreciated. Thank you!! 

[v-eqin-msft]

Hi @Chillytouch ,

You may try:

Measure  = 
VAR _sale=CALCULATE(SUM('Table'[Salary]),ALL('Table'))
var _revenue=CALCULATE(SUM('Table'[Revenue]),ALL('Table'))
return 
IF(HASONEVALUE('Table'[Fiscal Week]),[PayPercentRev], _sale/ _revenue )

If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Chillytouch 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.