Forum Discussion
Dividing all rows by a calculated value
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.
2 Replies
- amitchandak
Super User
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.
