Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have created an income statement, and want to divide every single row by the total sales row in a table that looks something like this:
Category | Sum of Total | DESIRED ANSWER |
Sales | $1,000.00 | |
Food | $240.00 | 24% |
Clothing | $56.00 | 6% |
Beverages | $60.00 | 6% |
Tickets | $644.00 | 64% |
COGS | $840.00 | 84% |
Food | $90.00 | 9% |
Beverages | $10.00 | 1% |
Paper | $40.00 | 4% |
Labor | $100.00 | 10% |
Rent | $600.00 | 60% |
Non operating expense | $90.00 | 9% |
Admin. | $50.00 | 5% |
Water | $40.00 | 4% |
Total | $70.00 | 7% |
However, each time I try to divide to get the desired answer, I only get the % of the sales components, not of all other rows. I know that for me to get the % for clothing for instance I have to do individual functions like:
But how can I create a formula that drags to all rows, and does not filter. So that in a column in the table that would be a row called % of total sales.
@JonathanK1999 , try like
% total sales = DIVIDE(CALCULATE(SUM(ConsolidadoPG[Sum of Total])), CALCULATE(SUM(ConsolidadoPG[Sum of Total]), ConsolidadoPG[Categoria]="Sales"))
or
% total sales = DIVIDE(CALCULATE(SUM(ConsolidadoPG[Sum of Total])), CALCULATE(SUM(ConsolidadoPG[Sum of Total]), Filter( all(ConsolidadoPG) , ConsolidadoPG[Categoria]="Sales")) )
or
% total sales = DIVIDE(CALCULATE(SUM(ConsolidadoPG[Sum of Total])), CALCULATE(SUM(ConsolidadoPG[Sum of Total]), Filter( allselected(ConsolidadoPG) , ConsolidadoPG[Categoria]="Sales")) )
Did not work, I am not getting the desired answer, and its just filtering for the values that fall under the sales sub category, such as food, clothing, beverages and tickets in the example above.