I have Product Categories in rows, CalendarYear in columns, and measure TotalProfit as part of PowerPivot fact table.
I want to calculate percent ratio of profit per category to sub total of the year column I use this formula:
=SUM(FactSales[TotalProfit]) / CALCULATE(SUM(FactSales[TotalProfit]),ALLEXCEPT(FactSales,FactSales[CalendarYear]))
But ratio is given woward grand total, not for subtotal sum in the year column.
Thanks for your help.
Bookmarks