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:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Product[/TD]
[TD="class: xl65, width: 64"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]E[/TD]
[TD="class: xl65, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl65"]F[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]E[/TD]
[TD="class: xl65, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Product[/TD]
[TD="class: xl65, width: 64"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]50[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65, align: right"]55[/TD]
[/TR]
[TR]
[TD="class: xl65"]E[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]F[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

This table would then need to be sorted by Sales:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Product[/TD]
[TD="class: xl65, width: 64"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65, align: right"]55[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]50[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]E[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65"]F[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 196"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Sales[/TD]
[TD]% of Total[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]31%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]28%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]14%[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]14%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]11%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3%[/TD]
[/TR]
</tbody>[/TABLE]

Finally, we would calculate the % of Running Total:

[TABLE="width: 320"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Sales[/TD]
[TD]% of Total[/TD]
[TD]% of Total Running[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]31%[/TD]
[TD="align: right"]31%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"]58%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]72%[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]86%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]97%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Product[/TD]
[TD="class: xl65, width: 64"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl66, align: right"]31%[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl66, align: right"]58%[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl66, align: right"]72%[/TD]
[/TR]
[TR]
[TD="class: xl65"]E[/TD]
[TD="class: xl66, align: right"]86%[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl66, align: right"]97%[/TD]
[/TR]
[TR]
[TD="class: xl65"]F[/TD]
[TD="class: xl66, align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Shop[/TD]
[TD="class: xl63, width: 64"]Product[/TD]
[TD="class: xl63, width: 64"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]E[/TD]
[TD="class: xl63, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]F[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]E[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

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.