Gross Margin Calculation

[Anonymous]

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

[JarroVGIT · Accepted solution]

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! 🙂

[Anonymous]

@JarroVGIT  Brilliant!!!  Thank you for that.