Forum Discussion
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.
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 |
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! 🙂
2 Replies
- JarroVGIT
Resident Rockstar
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! 🙂
JarroVGIT Brilliant!!! Thank you for that.