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 ...
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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

        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!