Hi,
I am segmenting my data by cumulative/pareto sales in this way:
The customers and products that make up the first 80% of sales are labeled as ‘A’ customers or products. The tail 20% of customers are labeled as ‘B’ customers or products. I then put these segments together to make four quads of my sales:
Quad1 ‘best’ A customer buying A product.
Quad2 A customer buying B product.
Quad3 B customer buying A product.
Quad4 B customer buying B product.
Being able to segment this data in a way that allows me to use the segments as a filter is the valuable part. I have been able to achieve this for the full data set using calculated columns shown in the first board in the attached PBIX file. However, I want to also be able to do this dynamically for different selections of the data. For example, I want to select a different year and have it automatically update the ‘quads’ for that year. I have been able to do this dynamic segmenting using supporting tables “Customer Group” and “Product Group” and the below DAX however when I select the groups in the Pie chart it doesn’t filter my customer or products. I’ve tried using IF instead of CALCULATE to maintain the filtering ability but I haven’t been successful:
Customer Sales by Group =
CALCULATE( [Total Sales],
FILTER( VALUES( Customers[Customer Name] ),
COUNTROWS(
FILTER( ‘Customer Group’,
[Pareto Customer] > ‘Customer Group’[Min]
&& [Pareto Customer] <= ‘Customer Group’[Max] ) ) > 0 ) )
Product Sales by Group =
CALCULATE( [Total Sales],
FILTER( VALUES( Products[Product ID] ),
COUNTROWS(
FILTER( ‘Product Group’,
[Pareto Product] > ‘Product Group’[Min]
&& [Pareto Product] <= ‘Product Group’[Max] ) ) > 0 ) )
Hopefully that makes sense and is something that is possible in Power BI. Thank you! Ezra
Sample Data.xlsx (677.6 KB)
Export Sample.pbix (574.6 KB)