Forum Discussion
Gross Margin Column For Customer without Creating New Table
Hello, I am looking to create a column that has the total gross margin for the customer on each row. This is transactional data, so the customers are not unique in the columns. How I would typica...
Anonymous
Here's the COLUMN formula that should do it
GM % COLUMN = DIVIDE ( CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) ) - CALCULATE ( SUM ( 'Table'[Cost] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) ), CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) ), 0 )Hope this helps! :smileyhappy:
Thanks for the response. I am wanting to actually have these as columns in the values, not just a visual. ie getting the last column to calculate in the values tab:
| Customer | Item | Sales | Cost | Customer GM |
| A | 1 | 80 | 50 | 50.00% |
| A | 2 | 100 | 40 | 50.00% |
| B | 1 | 75 | 50 | 45.95% |
| B | 2 | 110 | 50 | 45.95% |
Anonymous
Here's the COLUMN formula that should do it
GM % COLUMN =
DIVIDE (
CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) )
- CALCULATE ( SUM ( 'Table'[Cost] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) ),
CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) ),
0
)Hope this helps! :smileyhappy:
Just what I wanted. Thanks!