Forum Discussion

apatwal's avatar
apatwal
Icon for Helper III rankHelper III
4 years ago

Help in building DAX to create Filter Visual

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?

8 Replies

  • vikrambasriyar's avatar
    vikrambasriyar
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      apatwal
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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!