Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Margin Clusters

Hi! Unpivoted table: I have a column with custumers ID, and another one that says if it's revenue, margin, or margin %. So for each account I have 3 rows, where in the 2nd column it says rev, mar, o...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    Create columns

    margin_value = CALCULATE(MAX([value]),FILTER(ALLEXCEPT(Sheet1,Sheet1[custumers ID]),[condition]="margin %"))

category = IF([margin_value]>=0&&[margin_value]<0.1,"0~10%",IF([margin_value]<0.2,"10%~20%",IF([margin_value]<0.3,"20%~30%","+30%")))

    then create a measure

    revenue_sum = CALCULATE(SUM(Sheet1[value]),FILTER(Sheet1,[condition]="revenue"))

     

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous

Create columns

margin_value = CALCULATE(MAX([value]),FILTER(ALLEXCEPT(Sheet1,Sheet1[custumers ID]),[condition]="margin %"))

category = IF([margin_value]>=0&&[margin_value]<0.1,"0~10%",IF([margin_value]<0.2,"10%~20%",IF([margin_value]<0.3,"20%~30%","+30%")))

then create a measure

revenue_sum = CALCULATE(SUM(Sheet1[value]),FILTER(Sheet1,[condition]="revenue"))

 

 

Best Regards

Maggie