Forum Discussion

apatwal's avatar
apatwal
Helper III
4 years ago

Customer Categorization based on Revenue

Hi,

 

Need to create a filter visual customer category based on Revenue.

Category are based on parent customer's revenue (which is the aggregate of all its child customers) i.e. aggregating invoice revenue at the parent customer level, rather than at the individual customer level.

Category are based on % of revenue. i.e., sort by revenue high to low, and take all the customers whose revenue makes up the top 50% of total revenue - this is Category 1.

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

 

We need to create below two filters based on above logic

Location Category : treats each location as separate

Aggregate Category : combines all customers, ignoring location

 

Any help on how to create DAX will be highly appreciated.

Thanks in Advance!

 

tamerj1 

DAX
DAX Commands and Tips
Help Requested

14 Replies

  • karnold's avatar
    karnold
    Resolver I
    4 years ago

    You should review the following DAX Patterns, I think they will lead you to a solution

    Static segmentation – DAX Patterns

    Dynamic segmentation – DAX Patterns

     

    You might also be able to solve it in your data pipeline before it gets into the PBI model. Here is my Blog post that looks into different ways of doing segmentation Dynamic Segmentation – Kevin Arnold

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi apatwal 
    Here is the sampel file with the solution https://we.tl/t-KMt9n9cqPl

    This is how the report looks like 

    The measures are

     

    Total Revenue = SUM ( 'Main data'[Revenue] )
    Category Total Revenue = 
VAR RevenueByParentCustomer =
    ADDCOLUMNS (
        ALLSELECTED ( 'Main data'[Parent Name] ),
        "@ParentCustomerRevenue", [Total Revenue]
    )
VAR AllRevenue =
    CALCULATE (
        [Total Revenue],
        ALLSELECTED ( 'Main data'[Parent Name] )
    )
VAR CumulatedPercentByParentCustomer =
    ADDCOLUMNS (
        RevenueByParentCustomer,
        "@CumulatedPercentage",
        VAR CurrentTotalRevenue = [@ParentCustomerRevenue]
        VAR CumulatedRevenue =
            FILTER (
                RevenueByParentCustomer,
                [@ParentCustomerRevenue] >= CurrentTotalRevenue
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedRevenue,
                [@ParentCustomerRevenue]
            )
        RETURN
            DIVIDE (
                CumulatedSalesAmount,
                AllRevenue
            )
    )
VAR ParentCustomerInCategory =
    FILTER (
        CROSSJOIN (
            CumulatedPercentByParentCustomer,
            'Categories'
        ),
        AND (
            [@CumulatedPercentage] > 'Categories'[Lower Boundary],
            [@CumulatedPercentage] <= 'Categories'[Upper Boundary]
        )
    )
VAR Result =
    CALCULATE (
        [Total Revenue],
        KEEPFILTERS ( ParentCustomerInCategory )
    )
RETURN
    Result
    Count of Parent Customers = 
VAR RevenueByParentCustomer =
    ADDCOLUMNS (
        ALLSELECTED ( 'Main data'[Parent Name] ),
        "@ParentCustomerRevenue", [Total Revenue]
    )
VAR AllRevenue =
    CALCULATE (
        [Total Revenue],
        ALLSELECTED ( 'Main data'[Parent Name] )
    )
VAR CumulatedPercentByParentCustomer =
    ADDCOLUMNS (
        RevenueByParentCustomer,
        "@CumulatedPercentage",
        VAR CurrentTotalRevenue = [@ParentCustomerRevenue]
        VAR CumulatedRevenue =
            FILTER (
                RevenueByParentCustomer,
                [@ParentCustomerRevenue] >= CurrentTotalRevenue
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedRevenue,
                [@ParentCustomerRevenue]
            )
        RETURN
            DIVIDE (
                CumulatedSalesAmount,
                AllRevenue
            )
    )
VAR ParentCustomerInCategory =
    FILTER (
        CROSSJOIN (
            CumulatedPercentByParentCustomer,
            'Categories'
        ),
        AND (
            [@CumulatedPercentage] > 'Categories'[Lower Boundary],
            [@CumulatedPercentage] <= 'Categories'[Upper Boundary]
        )
    )
VAR Result =
    CALCULATE (
        COUNTROWS ( VALUES ( 'Main data'[Parent Name] ) ),
        KEEPFILTERS ( ParentCustomerInCategory )
    )
RETURN
    Result
    Category = 
IF (
    HASONEVALUE ( 'Main data'[Parent Name] ),
    VAR RevenueByParentCustomer =
        ADDCOLUMNS (
            ALLSELECTED ( 'Main data'[Parent Name] ),
            "@ParentCustomerRevenue", [Total Revenue]
        )
VAR AllRevenue =
    CALCULATE (
        [Total Revenue],
        ALLSELECTED ( 'Main data'[Parent Name] )
    )
    VAR CurrentRevenue = [Total Revenue]
    VAR CumulatedRevenue =
        FILTER (
            RevenueByParentCustomer,
            [@ParentCustomerRevenue] >= CurrentRevenue
        )
    VAR CumulatedTotalRevenue =
        SUMX (
            CumulatedRevenue,
            [@ParentCustomerRevenue]
        )
    VAR CurrentCumulatedPercentage =
        DIVIDE (
            CumulatedTotalRevenue,
            AllRevenue
        )
    VAR Result =
        SWITCH (
            TRUE,
            ISBLANK ( CurrentCumulatedPercentage ), BLANK (),
            CurrentCumulatedPercentage <= 0.5 || AND ( COUNTROWS ( CumulatedRevenue ) = 1, CurrentCumulatedPercentage > 0.5 ), "Category 1",
            CurrentCumulatedPercentage > 0.5 && CurrentCumulatedPercentage <= 0.8, "Category 2",
            CurrentCumulatedPercentage > 0.8 && CurrentCumulatedPercentage <= 0.95, "Category 3",
            "Category 4"
        )
    RETURN
        Result
)
    Count of Customers = DISTINCTCOUNT ( 'Main data'[Customer Name] )

     

    • apatwal's avatar
      apatwal
      Helper III
      4 years ago

      Hi tamerj1 

       

      For Location A, we don't have Category 1 as customer segment. Category 1 encompasses the top 50% of revenue in other words, if Total Revenue is $1M, then catgory 1 customers would be top N that makes up the top $500K of revenue. Category 2 encompasses 20% to 50% of revenue and so on.

       

      Also, to note : this categorisation should treat each location as separate and also, we need to built one more category where location are ignored in that which can be called as Overall categorisation.

      Could you please look into this.?

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        apatwal 

        Yes  noticed that. I'll see what I can do. 
        regarding the location, it is the same. If unselect the location then it will consider the classification as overall. Unless you need to show at the same report