cancel
Showing results for
Did you mean:
Highlighted
Helper I

Gross Margin Calculation

Hi I am trying to calculate the Gross Margin of Revenue / Non Revenue line items, my formula is below however it is not producing the desired result, hoping someone can help.

Gross Margin =
DIVIDE(
sum('Fact'[Actual])
,CALCULATE(sum('Fact'[Actual]), FILTER('Grouping','Grouping'[DBC_Grouping] = "Rental Revenue"))
,BLANK()
)

Desired Output:

 Row Labels Sum of Actual Gross Margin Revenue 261769796.6 100.00% Salaries -18338513.32 -7.01% Utilities -25995159.38 -9.93% Repairs & Maintenance -22138558.91 -8.46% Other Property Operating -53842110.66 -20.57% Grand Total 141455454.4 54.04%

Output based on current formula:

 Row Labels Sum of Actual Gross Margin Revenue 261769796.6 100.00% Salaries -18338513.32 Utilities -25995159.38 Repairs & Maintenance -22138558.91 Other Property Operating -53842110.66 Grand Total 141455454.4
1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

Re: Gross Margin Calculation

Try using ALL() in your filter statement;

DIVIDE(
sum('Fact'[Actual])
,CALCULATE(sum('Fact'[Actual]), FILTER(ALL('Grouping'),'Grouping'[DBC_Grouping] = "Rental Revenue"))
,BLANK()
)

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

2 REPLIES 2
Super User I

Re: Gross Margin Calculation

Try using ALL() in your filter statement;

DIVIDE(
sum('Fact'[Actual])
,CALCULATE(sum('Fact'[Actual]), FILTER(ALL('Grouping'),'Grouping'[DBC_Grouping] = "Rental Revenue"))
,BLANK()
)

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

Helper I

Re: Gross Margin Calculation

@JarroVGIT  Brilliant!!!  Thank you for that.

Announcements

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors