Calculated table using DAX...

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
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
 
Interesting!

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

Code:
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)
    )
)
 
Last edited:
Upvote 0
Code:
Cumulative Sales = 
CALCULATE(
 [Total Sales],
 topn(
  [Product Rank],
  ALL(Table1[Product]),
  [Total Sales]
 )
)
 
Upvote 0
That's great, gazpage! Many thanks for showing me the way. I am slowly getting my head around DAX... I think!

Cheers,

Matty
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top