Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chillytouch
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. 

 

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())))
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
v-eqin-msft
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'))
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
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.


Regards

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.