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.
Regards,
------------------------------
Adam Artur Boltryk
Business Analyst
------------------------------
Original Message:
Sent: Apr 10, 2022 12:22 PM
From: Jeff Nixon
Subject: Dynamic AB segmentation by BOTH customer and product - EXPERT Challenge
Greetings,
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
------------------------------
Jeff Nixon
Managing Director
Jefferson Rand, LLC
------------------------------