Forum Discussion

Jimmy_85's avatar
Jimmy_85
New Member
6 years ago

Measure segmentation in ranges based on another table

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 t...
Need Help
v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

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's avatar
    Jimmy_85
    New Member
    6 years ago

    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!