Help in building DAX to create Filter Visual

[apatwal]

Hi,

I am new to Power BI DAX and need help in creating Customer Category based on Revenue

Category 1: Top 50% of customers per location based on their total revenue

Category 2: Customers in 20% to 50% range

Category 3: Customers in 5% to 20%  range

Category 4: Bottom 5% of customers

Below is the sample data

First, it would sum all revenue of each customer per location then rank and then we have to categorise.

I am not sure how to write DAX for this.
Can someone help me on this?

[vikrambasriyar]

Use this DAX in a calculated column of your table : Replace Table name  with whatever you have named your table

Category = 

VAR __total=DISTINCTCOUNT([Location])
VAR __RANK=
RANKX(Category,CALCULATE(SUM(Category[Revenue]),ALLEXCEPT(Category,Category[Location])),,DESC,DENSE)
RETURN

SWITCH(TRUE(),__RANK>=__total*.50,"Category 1",
__RANK>=__total*.20 && __RANK<__total*.50,"Category 2",
__RANK>=__total*.05 && __RANK<__total*.20,"Category 3", 
__RANK<__total*.05,"Category 4")

[apatwal]

Hi @vikrambasriyar 

Thanks for your reply!

Your DAX works fine but I need to treat each location separately i.e. when ranking total revenue treat each location separately. Currently, all locations are combined together and then customer categorisation is done.

Consider we have 10 location in our dataset then categorisation should be done location wise like

Location A top 50% Catgeory 1, 20%-50% to Category B....

same for Location B top 50% Catgeory 1, 20%-50% to Category B....

Right now, all locations are considered together which should not be done.

Sorry if I misunderstood anything in my previous post.

[tamerj1]

Hi @apatwal 
This is a standard ABC analysis. Please refer to the file with solution https://www.dropbox.com/t/6x4VGdXMIxCow8sB
Basically you need to create 3 calculated Columns in the same following order

Incremental Revnue = 
VAR CurrentReveneue = 
    Data[Revenue]
VAR CurrentLocation = 
    Data[Location]
VAR FilteredTable =
    FILTER ( 
        Data, 
        Data[Revenue] >= CurrentReveneue
            && Data[Location] = CurrentLocation
    )
VAR Result = 
    SUMX (  
        FilteredTable,
        Data[Revenue] 
    )
RETURN 
    Result

Incremental Percentage = 
VAR CurrentRevenue = 
    Data[Revenue]
VAR CurrentLocation = 
    Data[Location]
VAR FilteredTable =
    FILTER ( 
        Data, 
        Data[Location] = CurrentLocation
    )
VAR TotalRevenuePerLocation = 
    SUMX (  
        FilteredTable,
        Data[Revenue] 
    )
VAR Result =
    DIVIDE ( Data[Incremental Revnue], TotalRevenuePerLocation )
RETURN 
    Result

ABC Category = 
SWITCH (
    TRUE,
    Data[Incremental Percentage] <= 0.50, "Category 1",
    Data[Incremental Percentage] <= 0.70, "Category 2",
    Data[Incremental Percentage] <= 0.95, "Category 3",
    "Category 4"
)

Your table looks like this.

And you can use this column to create slicers or other visuals.
Please let me know if this answers your query. If so, please consider marking this reply as acceptable answer. Thank you!

[Anonymous]

Hi @apatwal ,

You want to group by location, sort by revenue within each region, and output categories, right?

Please check my measure.

Category =
VAR _total =
    CALCULATE (
        COUNT ( 'Table'[Location] ),
        ALLEXCEPT ( 'Table', 'Table'[Location] )
    )
VAR _RANK =
    RANKX (
        FILTER ( ALLSELECTED ( 'Table' ), [Location] = MAX ( 'Table'[Location] ) ),
        CALCULATE ( SUM ( 'Table'[Revenue] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    SWITCH (
        TRUE (),
        _RANK >= _total * .50, "Category 1",
        _RANK >= _total * .20
            && _RANK < _total * .50, "Category 2",
        _RANK >= _total * .05
            && _RANK < _total * .20, "Category 3",
        _RANK < _total * .05, "Category 4"
    )

I added a few rows to your original data.

Best Regards,

Stephen Tao

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

[apatwal]

Hi @Anonymous 

Thanks for your reply!

You are correct but I need to create visual filter using this.

Is it possible to create calculate column here instead of measure?

Also, I saw in your screenshot provided, marked in red MUM C01 with 109 Revenue should be in Category 2 not in Category 1

Appreciate your help!

[Anonymous]

Hi @apatwal ,

Sorry I didn't read your request carefully.

Here's the calculated column.

Category =
VAR _total =
    CALCULATE (
        COUNT ( 'Table'[Location] ),
        ALLEXCEPT ( 'Table', 'Table'[Location] )
    )
VAR _RANK =
    RANKX (
        FILTER ( 'Table', [Location] = EARLIER ( 'Table'[Location] ) ),
        CALCULATE (
            SUM ( [Revenue] ),
            FILTER (
                'Table',
                [Customer] = EARLIER ( 'Table'[Customer] )
                    && [Location] = EARLIER ( 'Table'[Location] )
            )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
    SWITCH (
        TRUE (),
        _RANK >= _total * .50, "Category 1",
        _RANK >= _total * .20
            && _RANK < _total * .50, "Category 2",
        _RANK >= _total * .05
            && _RANK < _total * .20, "Category 3",
        _RANK < _total * .05, "Category 4"
    )

Best Regards,

Stephen Tao

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

[apatwal]

Hi @Anonymous 

This is not working for my dataset. 

It gives me value only Category 3 and Category 4

I also have some visual filters in my report does that affect this category column not sure. 😞

Just to add, we are taking sum of revenue for all dates for each customer location wise but still the customer categorisation is wrong.

[amitchandak]

@apatwal , Are you looking for percentile ?

https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/