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, 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

 

then 

 

here is pbix, please try it.

https://www.dropbox.com/s/5gejki6x0fg2ffc/Group%20Measure%20values%20on%20rows.pbix?dl=0

 

Best Regards,

Lin

BruceAnderson's avatar
BruceAnderson
Helper II
7 years ago

I have given it a go.

Unfortunately only 2 of the 4 ranking groups are showing.  Even though there are results for the other two groups.

What I was not too sure about was your step 2).  This is only for a visual check, and not required for the required result correct?

 

" Step2: Drag field rank and this measure into table visual"

 

  • 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.