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

 

[gazpage]

Interesting!

RANKX() give you the rank of a particular item by category, so:

Total Sales:=
SUM(Sales)

Product Rank:=
RANKX(
    ALL(Table[Product]),
    [Total Sales]
)

Running Total:=
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(Table[Product]),
        [Product Rank]<=VALUES([Product Rank])
    )
)

Running Percent:=
DIVIDE(
    [Running Total],
    CALCULATE(
        [Total Sales],
        ALL(Table)
    )
)

 

[gazpage]

Actually step 3 won't work. Not sure how to filter by rank, probably TOPN.

 

[gazpage]

Cumulative Sales = 
CALCULATE(
 [Total Sales],
 topn(
  [Product Rank],
  ALL(Table1[Product]),
  [Total Sales]
 )
)

 

[Matty]

That's great, gazpage! Many thanks for showing me the way. I am slowly getting my head around DAX... I think!

Cheers,

Matty

 

[Matty]

Hi,

All works well, but my real data actually has a hierarchy associated with it, which I want to be able to slice on and then see the appropriate data.

Below is an example of the data layout:

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

<tbody>
</tbody>

So, the solution provided works well, but if I slice on Shop, the Running Percent doesn't account for the fact I'm looking at a subset of the data (Shop 2).

I've had a play around with FILTER, but I can't get things to work unless I hard code the Shop number into the DAX formula.

Any ideas?

Cheers,

Matty

 

[gazpage]

Likely just overzealous use of ALL() in my code. Anywhere you are using ALL() make sure it includes the column name i.e. ALL(Table[Product]).

If that doesn't fix it could you please drop the four measures into a table and let us know at which step it goes wrong?

 

[gazpage]

Seems my internet is down, but if changing the column reference doesn't work then also try replacing ALL() with ALLSELECTED() (only at the step it goes wrong).

 

[Matty]

Likely just overzealous use of ALL() in my code. Anywhere you are using ALL() make sure it includes the column name i.e. ALL(Table[Product]).

If that doesn't fix it could you please drop the four measures into a table and let us know at which step it goes wrong?

The overzealousness was to blame! Thanks!

Matty

 

[gazpage]

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.