CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
mayool
mayool

Reputation: 148

Saving DAX Meassure to table (ABC /Pareto analysis)

I am currently working on an abc/pareto analysis concerning customer IDs.

What I want to calculate is something like this:

ID| Sales / ID |Cum. Sales|  %from total | category
G   15.000,00€  15.000,00 €   21,45%      A
D   5.700,00€   20.700,00 €   29,60%      A
H   4.000,00€   24.700,00 €   35,32%      A
Q   3.800,00€   28.500,00 €   40,75%      A
O   3.650,00€   32.150,00 €   45,97%      A
X   3.500,00€   35.650,00 €   50,97%      B
I   3.350,00€   39.000,00 €   55,76%      B
Ü   3.200,00€   42.200,00 €   60,34%      B
Ö   3.050,00€   45.250,00 €   64,70%      B
N   2.900,00€   48.150,00 €   68,84%      B
J   2.750,00€   50.900,00 €   72,78%      C
Ä   2.600,00€   53.500,00 €   76,49%      C
Z   2.450,00€   55.950,00 €   80,00%      C
Y   2.300,00€   58.250,00 €   83,29%      C
L   2.150,00€   60.400,00 €   86,36%      D
P   2.000,00€   62.400,00 €   89,22%      D
W   1.765,00€   64.165,00 €   91,74%      D
R   1.530,00€   65.695,00 €   93,93%      D
F   1.295,00€   66.990,00 €   95,78%      E
V   1.060,00€   68.050,00 €   97,30%      E
B   825,00€     68.875,00 €   98,48%      E
T   590,00€     69.465,00 €   99,32%      E
M   355,00€     69.820,00 €   99,83%      E
C   120,00€     69.940,00 €   100,00%     E

This way I can say that "A-customers" make 50% of my total profit.

I used this tutorial to create my meassures: https://www.youtube.com/watch?v=rlUBO5qoKow

total_sales = SUM(fact_table[sales])

cumulative sales =
    VAR MYSALES = [total_sales]
RETURN

SUMX(
    FILTER(
        SUMMARIZE(ALLSELECTED(fact_table);fact_table[CustomerID];
            "table_sales";[total_sales]);
                [total_sales] >= MYSALES);
                    [table_sales])

Since I am calculating the cumulative sales for >1000 unique customer IDs the calculation takes ages! Is there a way I can save this calculation in a new table so I only have to calculate it once? Or does anyone know a Meassure that does the same but is less computationally expensive?

Any help is much appreciated!

Upvotes: 0

Views: 300

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You could calculate it once as a calculated column but then ALLSELCTED wouldn't act as you expect since calculated columns cannot be responsive to report filters or slicers.

There are some inefficiencies in your measure though. It looks like you are calculating [total_sales] twice, once inside SUMMARIZE and again for the FILTER.

I haven't tested this measure, but it may be faster as follows:

cumulative sales =
VAR MYSALES = [total_sales]
RETURN
    SUMX (
        FILTER (
            SUMMARIZECOLUMNS (
                fact_table[CustomerID];
                ALLSELECTED ( fact_table );
                "table_sales"; [total_sales]
            );
            [table_sales] >= MYSALES
        );
        [table_sales]
    )

The important part is reusing [table_sales] in the FILTER but SUMMARIZECOLUMNS might be a bit better too.

Upvotes: 1

Related Questions