Measure segmentation in ranges based on another table

[Jimmy_85]

Dear All,

I need your help to the below: I have a classic database (Input table 1) with Sales/Cost data with different hierarchy levels for product and geography. The first step is to calculate the Gross Margin (GM) as calculated measure (defined as 1-Cost/Sales). On top I have another input table (Input table 2) which classifies the status of a product category based on the GM. The two desired tables are: Output table 1: A color coding indicating the status of the GM (I managed till now with switch/true but without the product category dimension in input table 2). Output table 2: Indicate the % of sales that are under each GM status. I managed this with the formula at the bottom, but again without the product category dimension. Of note that both output tables need to have the capability of drill up/drill down for both geography and product hierarchies.

Thanks a million, 

Jimmy

Input Tables
Table 1: Simple Database
Country	Product Sector	Product Category	SKU	Sales	Cost
Country X	Fruits	Banana	4242	10	5
Country Y	Vegetables	Tomato	3233	8	6
Country Y	Fruits	Apples	8578	20	12
Country Y	Vegetables	Cucumber	9489	8	2
Country Z	Fruits	Apples	8578	10	4
Country Z	Vegetables	Cucumber	9489	20	10
Table 2: Gross Margin% Range of a calculated measure
Product Category	Status	Upper Limit	Lower Limit
Apples	Green	100%	50%
Apples	Red	50%	30%
Cucumber	Green	100	60%
Cucumber	Red	60%	30%
Desired Output
Table 1: Color Coding based on Gross Margin%
Product Category	Country Y	Country Z
Apples	Red	Green
Cucumber	Green	Red
Table 2: % Sales under different GM% Status
Product Category	Green	Red	Total
Apples	33%	67%	100%
Cucumber	29%	71%	100%

GM% Cluster =

CALCULATE (sum(Table1[Sales]),

    FILTER (

        VALUES (Table1) ,

        COUNTROWS (

            FILTER (

                'Table2',

                [GM%]>= 'Table2'[Lower Limit]

                && [GM%] < 'Table2'[Upper Limit]

            )

        ) > 0

    )

)

[v-zhenbw-msft]

Hi @Jimmy_85 ,

We can use the following steps to meet your requirement.

1. Create a GM calculate column in Table1.

GM = 1 - DIVIDE([Cost],[Sales])

2. Create a country column in Table2.

country = CALCULATE(MAX('Table1'[Country]),FILTER('Table1','Table1'[Product Category]='Table2'[Product Category ] && 'Table1'[GM]>='Table2'[Lower Limit] && 'Table1'[GM]<='Table2'[Upper Limit]))

Then we can get the output table1.

3. We need to create a column in Table2 to calculate the sales.

Sales = CALCULATE(SUM(Table1[Sales]),FILTER('Table1','Table1'[Product Category]='Table2'[Product Category ] && 'Table1'[GM]>='Table2'[Lower Limit] && 'Table1'[GM]<='Table2'[Upper Limit]))

4. At last we can create a measure and get the output table2.

GM% Cluster = 
VAR total =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
             Table1 ,
            COUNTROWS (
                FILTER (
                    ALLSELECTED('Table2'),
                    [GM] >= 'Table2'[Lower Limit]
                        && [GM] < 'Table2'[Upper Limit]
                )
            ) > 0
        )
    )
var each_s = CALCULATE(SUM(Table2[Sales]))
RETURN
DIVIDE(each_s,total)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy_85]

Hello All,

Thank you very much for your quick responses! 

@amitchandak unfortunately this was my first effort but didn't finally work

@v-zhenbw-msft thank you for the detailed effort - please see below why this solution cannot work:

• GM% cannot be in a calculated column as I need this as a measure not a by row calculation. My original database has 20 countries, 5 product sectors, 50 product categories and 3,000 SKUs. And I need to do this calculation at any level of selection. 
• Due to the complexity of the database I cannot use the GM% range table to assign next to it the Country and the GM%. 
• In your final output 2 table, the total by row should be 100%. The logic is what % of Sales is under Red and under Green status, hence the sum should be 100%. 
• The desired output tables are exactly the ones that I have put above, with only difference that I have originally a much bigger database vs. this simplistic one. 

Thank you!

[amitchandak]

@Jimmy_85 , refer id these can help in Segmentation
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/