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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I tried creating a new column with that dax formula:

Cluster_Mar_% = IF([Margin_%] < 0.1,"0-10%", if([Margin_%] < 0.2,"10-20%",IF([Margin_%] < 0.3,"20-30%", if([Margin_%] > 0.3,"+30%"))))
 
But in the result of this column all Clust_Mar_% info is on the first cluster (0-10%), idk why...