Forum Discussion

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

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

v-lili6-msft

Community Support

7 years ago

hi,@BruceAnderson

After my research, you can do these follow my steps as below

Step1:

add a measure like below

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

Step2:

Drag field rank and this measure into table visual

Result:

Basic data