Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Dynamic AB segmentation by BOTH customer and product - EXPERT Challenge

    Silver Contributor
    Posted Apr 10, 2022 12:22 PM


    I am trying to apply the Russo/Ferrari pattern (Dynamic segmentation – DAX Patterns) extended to a second entity.

    The model has a single Fact table and three Dim tables, as well as tables for Products and Customers (80:20 tables) in which the cumulative sales cutoff is defined (0-0.8 of sales= A and 0.8-1.0 = B).  So, the Customers that together represent 80% of Sales ([Sales USD] in measures table) will be "A" and the rest "B".  Same for Products.

    I want to display a matrix that has Sales USD by Product Class (A or B) in columns and Customer Class (A o B) in rows.

    In the attached report, one can see that the customer "Aat" appears in both A and B customers (A for sales of product "B010" and B for sales of "G004"  But a Customer should be either an "A" or a "B", but not both.  Same for products (e.g. "B009" appears as A and B product).  So clearly I'm doing something wrong.

    The reason for a Dynamic measure is that I need to be able to slice Customer or Product by other Attributes, such as Market segment or Product line and have the cutoffs recalculated base on the filtered dataset.

    In addition to the pbix, I've attached the xls source, which I placed in a D:/Temp folder for the Power Query.

    Thanks for any insight into this complex problem.

    Kind regards,

    Jeff Nixon
    Managing Director
    Jefferson Rand, LLC


    AB analysis.xlsx   142 KB 1 version
    AB analysis.pbix   153 KB 1 version

  • 2.  RE: Dynamic AB segmentation by BOTH customer and product - EXPERT Challenge

    Posted Apr 12, 2022 02:05 PM

    Hi Jeff,
    If I good understand your requirements, it can not be done. It's classical problem how to have cake and eat cake.

    If you have 80-20 pareto by CUSTOMERS you can not mix it with pareto 80-20 PRODUCT.

    Why? Because  you try to mix apples and chips.

    It's reason why you see same results in both categories.

    You can see, that you don't have duplication for same Customer and same product - they are always in ONE section.
    Good news then - your measures works perfectly well :)
    In situation like this, I would prepare 2 IDENTICAL pages - one with Customer perspective. One with Product perspective.


    Adam Artur Boltryk
    Business Analyst

  • 3.  RE: Dynamic AB segmentation by BOTH customer and product - EXPERT Challenge

    Posted Apr 15, 2022 04:55 AM

    Hi there, to make it simpler basically you should first calculate your sales, then do two iterations in two different measures, one your customer of segmentation, second your product over segmentations. After, use switch function and a toggle to choose one in your usual. Here is a good video that explains dynamic segmentation thoroughly 😊

    Theo Bv
    Data Analyst