Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Calculated field based on rollup of other fields.



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. 


report ss 1.png

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())))
DisplayColumn = "Revenue", FORMAT(Revenue,"$0,0"),
DisplayColumn = "Salary", FORMAT(Salary,"$0,0"),
DisplayColumn = "PayPercentRev", FORMAT(PayPercentRev,"0.0%"))



Any advice is appreciated. Thank you!! 
Community Support
Community Support

Hi @Chillytouch ,


You may try:

Measure  = 
VAR _sale=CALCULATE(SUM('Table'[Salary]),ALL('Table'))
var _revenue=CALCULATE(SUM('Table'[Revenue]),ALL('Table'))
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.

Super User
Super User

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.


Miguel Félix

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helpful resources

Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

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

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

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

Top Solution Authors