Forum Discussion

BruceAnderson's avatar
BruceAnderson
Helper II
7 years ago
Solved

Group Measure values on rows

I have a simple sales table per customer.  Customer No |  Customer Disc Group | Sales Amt   I need to show a table in Power BI based on it, showing the ranking but in groups.    1-20%       1,54...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi,@BruceAnderson

          After my research, Whether you have used slicer to filter data like below:

    before

    After

    IF so, you need to use ALLSELECTED Function and ALLEXCEPT Function in the measure like this

    Measure 2 = 
    var a=ADDCOLUMNS(Sales_analysis,"r",RANKX (
     ALLSELECTED(Sales_analysis[Customer No]),
     CALCULATE(SUM(Sales_analysis[Sales Amt]),ALLEXCEPT(Sales_analysis,Sales_analysis[Customer No])),
     ,
     0,
     DENSE
    )) return
    var _table=FILTER(GENERATE(a,'Ranking Buckets'),[r]>='Ranking Buckets'[Start]&&[r]<='Ranking Buckets'[End]) return
    CALCULATE(SUMX(_table,[Sales Amt]))

    Result:

    Best Regards,

    Lin