Can't create a dynamic group for "Top 50%", "Next 30%" and rest as "Others" based on Revenue

[Sid0609]

Hi,

I am trying to create a dynamic grouping based on "Top 50% customers", "Next 30% Customers" and rest grouped under "Others" based on my Revenue. This calculation should also change dynamically based on the filters on the page, so supposing I have a year filter and a category filter, on having "2022" year selected and "clothing" category, the list of customers I see should be grouped under "Top 50%", "Next 30%" and rest as "Others". Finally, in a matrix visual if I expand the "Top 50%" customers, I should be able to see the list of customers under this. 

Could you please help with the DAX measures for this type of scenario?

[Sid0609]

Attaching a sample file with some mocked up data. You can access the file here - PBI demo

[Ashish_Mathur]

Hi,

Your way of segmenting seems flawed.  In the attached image, for the clothing category, DEF accounted for > 50% of the total sales.  As per your rule, this client should be in the "Next 30% customers" category but in fact this client should be in the Top 50%.  Please clarify.

[zsss]

Replying for Sid0609,
Yes DEF should be in Top 50%, and AZU should be the in the Next 30%.

[Ashish_Mathur]

But why?  DEF has a sale contribution of 58%

[zsss]

Because it contributed to the Top 50%. The final result we would want is to show such that DEF contributed 58%, DEF would be in the Top 50% and also would be in the Next 30%, however showing only it 8% sales amount. Then the next sales would be in the Next 30%.

[Ashish_Mathur]

That does not make a lot of sense to me.  The same client cannot fall in two segments.  My opinion would be create segments based on sales ranking instead.

[zsss]

Hi Ashish
I've done the Top % requirement with this sample pbix however there are another issue we are facing.
The matrix table takes too long to load.

The Matrix Values is the same measure code as group product sales amount with different CALCULATE([PARAMETER])
are my dax not optimize or should I not use matrix.

https://drive.google.com/u/0/uc?id=1ou6R82zjTcFTaWUSgPyNyQCv2kZpuzkh&export=download


In my working file, Performance analyzer showing that it takes 90000ms to load the page.

[Sid0609]

Hi @Ashish_Mathur ,
Right now the data I shared with you is mocked up data and has no relevance to the actual data we are working with. I agree where you're coming from and I understand the data is skewed, but the intention here is to understand the logic and measures you're creating for segmenting the data. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[zsss]

Hi I'm very confused... I did the same measure as your code, it works on my Vendor table but not working on my Customer table. Is it because in the sample, factsale is linked with dim_category but my data model fact_sales doesn't have category, vendor has category but not my customer

and also if you could help me look at the sample pbix I uploaded on my previous, is my dax measure not optimize? Thankyou so much

[Ashish_Mathur]

Hi,

I do not understand.  Please post your PBI file with only the tables which are required and my suggested formulas written there.  Show the problem there clearly.

[zsss]

Hi, I think it broke... thankyou here the pbix
here 

[Ashish_Mathur]

I do not understand the probem.  What is not working?

[Sid0609]

Hi @Ashish_Mathur ,

I think what my colleague was mentioning was that when we apply your solution to our model, the segmentation and matrix visual is working for vendor dimension but on applying the same to customer dimension it fails. I believe it might be because of the way our model is built. Nevermind, thanks for your help. 

[Ashish_Mathur]

You are welcome.  I did not see any problem in the file that he shared.

[Ashish_Mathur]

Hi,

I'd like to try.  Share the download link of the PBI file.  Remove the tables/measures which are not required for solving this question.

[v-rongtiep-msft]

Hi @Sid0609 ,

Please have a try.

Create a column.

Year=year(table[date])

Then create 2 measures.

measure =
RANKX (
    ALL ( table ),
    CALCULATE (
        SUM ( table[revenue] ),
        FILTER ( ALL ( table ), table[year] = SELECTEDVALUE ( table[year] ) )
    ),
    ,
    asc
)

measure_re =
VAR _1 =
    MAXX (
        FILTER ( ALL ( table ), table[year] = SELECTEDVALUE ( table[year] ) ),
        [measure]
    )
VAR _2 = [measure] / _1
RETURN
    IF ( _2 >= 0.8, "Others", IF ( _2 <= 0.5, "Top 50%", "Next 30%" ) )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Sid0609]

Hi @v-rongtiep-msft ,
I have tried your solution. I dragged measure_re along with the customer names into a matrix visual, there seems to be some computational issue as the visual takes a long time to load and after loading every customer is grouped under "Others".

Also if this was to work, this will only identify each customer as "Top 50%, "Next 30%" and rest as "Others" but will not group them as intended. Please look at the screenshot to get a view of what I'm trying to achieve. This visual is filtered by Year and Category as slicers which you should see in the demo.pbix file.

Hope this is clearer 🙂 

[v-rongtiep-msft]

Hi @Sid0609 ,

Could you please provide some sample data?

How to Get Your Question Answered Quickly 

Best Regards
Community Support Team _ Rongtie

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