Calculated table using DAX...

[Matty]

Hi Team,

I'm looking for some help creating a calculated table using DAX to work out what Products make up the top N % of my portfolio based on Sales.

My raw data table is as follows:

Product	Sales
A	10
B	15
D	25
E	15
F	5
E	10
D	30
C	25
A	40
B	5

<tbody>
</tbody>

Running through the logical steps in my head, the next task would be to summarise Sales by Product:

Product	Sales
A	50
B	20
C	25
D	55
E	25
F	5

<tbody>
</tbody>

This table would then need to be sorted by Sales:

Product	Sales
D	55
A	50
C	25
E	25
B	20
F	5

<tbody>
</tbody>

Next would be to work out the % of Total each Product represents:

Product	Sales	% of Total
D	55	31%
A	50	28%
C	25	14%
E	25	14%
B	20	11%
F	5	3%

<tbody>
</tbody>

Finally, we would calculate the % of Running Total:

Product	Sales	% of Total	% of Total Running
D	55	31%	31%
A	50	28%	58%
C	25	14%	72%
E	25	14%	86%
B	20	11%	97%
F	5	3%	100%

<tbody>
</tbody>

Ultimately, all I am after in terms of my DAX table is the following:

Product	Sales
D	31%
A	58%
C	72%
E	86%
B	97%
F	100%

<tbody>
</tbody>

Hope the above makes sense and someone can set me on my way.

Thanks,

Matty

 

[Matty]

Yeah, my bad. If you end up filtering any of you products out it will go wrong again. In that case you'd need ALLSELECTED.

Cheers fella.

Matty