cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculated field based on rollup of other fields.

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!!
2 REPLIES 2
Community Support

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

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.

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors