Forum Discussion
Matrix table with two rows diffrent Aggregation
Hi All,
Thanks in Advance, Below matrix table Category and Product on rows values sales and % sales show values vertically
Need help on calculation for % sales prod1 35/159(total of all prod within cat1) etc
when in toal % sales 159/402(total of all category) etc
| Category | Product | Measures | |
| Cat1 | Prod1 | Sales | 35 |
| % Sales | 22% | ||
| Prod2 | Sales | 48 | |
| % Sales | 30% | ||
| Prod3 | Sales | 76 | |
| % Sales | 48% | ||
| Total | Sales | 159 | |
| % Sales | 40% | ||
| Cat2 | Prod4 | Sales | 84 |
| % Sales | 35% | ||
| Prod5 | Sales | 53 | |
| % Sales | 22% | ||
| Total | Sales | 243 | |
| % Sales | 60% | ||
| Total | Sales | 402 | |
| % Sales | 100% |
Thanks,
Sanjay
Hi, sanjaymithran
I'm sorry I misunderstood what you meant, I modified the measure and you can use the DAX below:
%Sale = VAR _Total = DIVIDE( CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Category])), CALCULATE(SUM([Sales]), ALL('Table')), 0 ) VAR _Category = DIVIDE( SUM([Sales]), CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Category])), 0 ) RETURN IF(ISINSCOPE('Table'[Product]),_Category,_Total)The following is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi, sanjaymithran
Based on your information, I create a sample table:
Then create measures, try the following dax:
% Sales within Category = DIVIDE( SUM([Sales]), CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Category])), 0 )% Sales of Total = DIVIDE( CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Category])), CALCULATE(SUM([Sales]), ALL('Table')), 0 )Put fields in matrix visual, here is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yongkang Hua,
Thanks for your quick response,I already did like that only but user want single field % Sales.
Product level % Sales within Category and Category level % Sales of Total in Single field because we have many % fields so if we use two for each table too height ,is it possible in single field?
Thanks,
Sanjay.
Hi, sanjaymithran
I'm sorry I misunderstood what you meant, I modified the measure and you can use the DAX below:
%Sale = VAR _Total = DIVIDE( CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Category])), CALCULATE(SUM([Sales]), ALL('Table')), 0 ) VAR _Category = DIVIDE( SUM([Sales]), CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Category])), 0 ) RETURN IF(ISINSCOPE('Table'[Product]),_Category,_Total)The following is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
