Summarise table but repeat values which are zero

[Goossensm]

Hi all,

I have a transactions table which I would like to summarise in such way, so I can define and fix a label (ABC) per customer for each month, based on the last twelve months turnover via the rank and cumulative % function. 

A customer = within first 80% of turnover, ranked by sales per customer on LTM basis

B customer = within 15% of turnover, ranked by sales per customer on LTM basis

C customer = within 5% of turnover, ranked by sales per customer on LTM basis

I have a separate calendar table which is linked to the transactions table.

Transactions table:

Invoice date	YearMonth (invoice date)	Customer		Turnover	ABC label
01/03/2020	Mar20	Customer 1		40
15/03/2020	Mar20	Customer 2		30
15/04/2020	Apr20	Customer 1		20
26/05/2020	May20	Customer 3		40
14/06/2020	Jun20	Customer 2		30

I would like to summarise the transaction table as follows:

YearMonth	Customer		LTM turnover customer	LTM turnover total	% of LTM turnover total	rank	cumulative
Mar20	Customer 1		40	70	57%	1	57%
Mar20	Customer 2		30	70	43%	2	100%
Mar20	Customer 3		0	70	0%	3	100%
Apr20	Customer 1		60	90	66%	1	66%
Apr20	Customer 2		30	90	34%	2	100%
Apr20	Customer 3		0	90	0%	3	0%
May20	Customer 1		60	130	46%	1	46%
May20	Customer 2		30	130	23%	3	100%
May20	Customer 3		40	130	31%	2	77%
Jun20	Customer 1		60	160	37%	1	37%
Jun20	Customer 2		60	160	37%	2	74%
Jun20	Customer 3		40	160	26%	3	100%

Then I would define a label for each customer for each month, based on the LTM turnover.

I would then use a 'lookupvalue' function to add the label in a new column into the transaction table.

But when I use a regular summarise or groupby function I only get the customers for a specific month, when the turnover in that month is not 0, when I actually need to repeat all customers for all months.

Thanks in advance for the help!

[v-janeyg-msft · Accepted solution]

Hi, @Goossensm 

According to your description, I made a sample. 

You need to create a YM column for sort in fact table, then create a new table with these columns and measures.

Like this:

YM = YEAR('Table'[Invoice date])*100+MONTH('Table'[Invoice date])

Table 2 = CROSSJOIN(DISTINCT('Table'[Customer]),DISTINCT('Table'[YM]))

Turnovercopy = 
SUMX (
    FILTER (
        ALL ( 'Table' ),
        [YM] = EARLIER ( 'Table 2'[YM] )
            && [Customer] = EARLIER ( 'Table 2'[Customer] )
    ),
    [Turnover]
)

Column 2 = 
VAR a =
    SUMX (
        FILTER (
            ALL ( 'Table 2' ),
            [Customer] = EARLIER ( 'Table 2'[Customer] )
                && [YM] <= EARLIER ( 'Table 2'[YM] )
        ),
        [Turnovercopy]
    )
RETURN
    IF ( a <> BLANK (), a, 0 )

Column 3 = 
SUMX (
    FILTER ( ALL ( 'Table 2' ), [YM] = EARLIER ( 'Table 2'[YM] ) ),
    [Column 2]
)

Column 4 = DIVIDE([Column 2],[Column 3])

Column 5 = 
RANKX (
    FILTER ( ALL ( 'Table 2' ), [YM] = EARLIER ( 'Table 2'[YM] ) ),
    'Table 2'[Column 4],
    ,
    DESC,
    SKIP
)

Measure5 = 
SUMX (
    FILTER (
        ALL ( 'Table 2' ),
        [YM] = SELECTEDVALUE ( 'Table 2'[YM] )
            && [Column 5] <= SELECTEDVALUE ( 'Table 2'[Column 5] )
    ),
    [Column 4]
)

Here is my sample file. Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Goossensm , Are you looking for segmentation or binning (bucket on the measure)

https://www.daxpatterns.com/abc-classification/

Or check this -https://www.youtube.com/watch?v=CuczXPj0N-k

[Goossensm]

Hi @amitchandak ,

The first link is already very helpful.

I am indeed looking for a snapshot abc classiciation for the customers. 

But instead of calculating it again each year, I would like to take a snapshot each month, and classify each customer for that specific month based on the last twelve months sales.

Kr,