Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
@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
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,
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
102 | |
93 | |
73 | |
60 | |
59 |