Matrix table with two rows diffrent Aggregation

[sanjaymithran]

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

[Anonymous]

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.

[sanjaymithran]

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.

[Anonymous · Accepted solution]

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.