Dynamic segmentation of customers based on the last few years

[Anonymous]

Hello,

I tried to create a dynamic segmentation (based on the DAX pattern from Marco Russo Link - Dynamic Segmentation) in Power BI for my dataset, but somehow it doesn't work as I needed. My goal is to grouping customers based on their maximum sales on the last few years. In the screenshot you can see the an example of the data set and the desired output. Attached you'll find the PBI file incl. the DAX I already created.

PBI file

Can someone help me to solve the issue?

Thank you very much in advance!

[theov]

Hi there, yes basically, first you should calculate your max sales for last few year, then iterate customer table over your segmentation table based on the first measure. The iteration measure is explained thoroughly in this video 🙂

https://www.youtube.com/watch?v=FDa0I3J3h-c

[v-xicai]

Hi  @Anonymous ,

Currently, return the result is correct when use your formula and filter [country]= "USA". While the expected output in your screenshot is in correct, the row which have the [Customer Size] =Customer 2 (350, 80) is always under Customer Size 501$-2000$.  Maybe you need to correct the Min and Max to keep consistent with the Customer Size.

>>My goal is to grouping customers based on their maximum sales on the last few years.

Could you please clarify what desired result would you want?

Best Regards,

Amy

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

[Anonymous]

Hello Amy,

thanks for your answer. I realized that I had a mistake in the segmentation table. Now I have fixed that in the PBI file:

>>Could you please clarify what desired result would you want?<<

Looking on the matrix without any filter the matrix shows for example the Customer 2 in all thre categories. But I want this customer just in category "501-2000":

Current matrix:

What I want to have:

Thank you very much!

Best Regards

Semih

[AlbertoFerrari · Accepted solution]

Here you are:

M = 
VAR CustomersMaxPerYear = 
ADDCOLUMNS(
    VALUES ( Data[Customer Name] ),
    "MaxSales",
    CALCULATE(
        MAXX ( 
            ALLSELECTED ( Data[Year] ),
            CALCULATE( SUM ( Data[Sales] ) )
        )
    )
)
VAR MinRange = MIN ( Segment[Min] )
VAR MaxRange = MAX ( Segment[Max] )
VAR CustomersInRange = 
FILTER (
    CustomersMaxPerYear,
    [MaxSales] > MinRange &&
    [MaxSales] <= MaxRange
)
RETURN
    CALCULATE ( SUM ( Data[Sales] ), CustomersInRange )

As a side note, your previous code shows that you need to spend some time with the basics... may I suggest to review the introducing DAX course, as a starter? It will gretaly help in your development, and it's free. 🙂

[Anonymous]

Hi Alberto,

Your proposed code is working perfect!

Thank you for sharing the link, I will for sure review the course! I am happy about all tips and tricks realted to PowerBI. At the moment I am reading your book "The defintive Guide to DAX", and its very helpful. Thanks for sharing your knowledge with the community!

Best Regards

Semih

[Laxman_V]

Thank you very much to you,  @AlbertoFerrari , @v-xicai , @amitchandak for introducing me to dynamic segmentation scenario.

I have a similar problem mentioned at https://community.powerbi.com/t5/Desktop/Slicer-selection-based-on-item-sub-totals/m-p/1840257#M7120...

To make it analogous with yours, I'll need [Sales by Segment] based on Country sales instead of customer sales; and [Customer Size] slicer should filter data based on it.

Could you please help me know the dax formula used at [Sales by Segment] measure and /or advise on updating this formula to calculate based Country sales instead of customer sales.

Thank you in advance,

Laxman