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

v-lili6-msft's avatar
v-lili6-msft
Community Support
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

BruceAnderson's avatar
BruceAnderson
Helper II
7 years ago

Thank you again.  I did have a slicer, but using a date. I tested disabling the slicer connection and it then showed all groups.

 

 

So I have modified it to include the ALLSELECTED and ALLEXCEPT and enabled the sclier .  Then it is showing the correct total based on slicer selection, but the backet group is all lumped in one group again.  I am sure I must have made a simple newbie mistake though.

 

 

Group Sales =
var a=ADDCOLUMNS(Sales_analysis,"r",RANKX (
 ALLSELECTED(Sales_Analysis[Posting Date 2]),
 CALCULATE(SUM(Sales_Analysis[Sales Amount]),ALLEXCEPT(Sales_Analysis,Sales_Analysis[Posting Date 2])),
    ,
    0,
    DENSE
)) return
var _table=FILTER(GENERATE(a,'Ranking Buckets'),[r]>='Ranking Buckets'[Start]&&[r]<='Ranking Buckets'[End]) return
CALCULATE(SUMX(_table,[Sales Amount]))

  • BruceAnderson's avatar
    BruceAnderson
    Helper II
    7 years ago

    I think I have resolved this (with your help of course). I was replacing "Customer No_" field with the slicer field "posting Date 2", which was incorrect.  I changed it to use Customer No_ as your DAX had, and now it seems to be working.   Many thanks for your assistance there I have learned a lot.