Group Measure values on rows

[BruceAnderson]

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

21-50%     1,043,878

51-150%      903,968

151-%          648,891

So I made a measure which does the ranking per customer, that works.

Sales Ranking = RANKX(All(Sales_analysis[Customer No_],Sales_Analysis[Customer Disc Group],Sales_Analysis[Name]), [Sales Amt],,0,DENSE)

I then need somehow to group that for the groups above.  I tried creating a "Bucket Table", and linked with this.

Ranking Group = CALCULATE(VALUES('Ranking Buckets'[Rank]), FILTER(ALL('Ranking Buckets'),Sales_Analysis[Sales Ranking] >= 'Ranking Buckets'[Start] && Sales_Analysis[Sales Ranking] <= 'Ranking Buckets'[End]))

However I only see the first row, I am unable to get the other groups.  Would anyone have any advice?

[v-lili6-msft]

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]

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 · Accepted solution]

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]

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]

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.

[BruceAnderson]

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"

 

[BruceAnderson]

Many thanks for that Lin!

I will give it a try.

[v-lili6-msft]

hi,@BruceAnderson

      Your description is not very clear, please share some data sample and expected output and some screenshot for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[BruceAnderson]

My apologies. This is the data source.  It is very simple, just a customer number and sales amount.


What we need to do is have it summarized by ranking and the total sales per each ranking as per below.

Rank Group    Total Sales Amount
1-20%             1,544,890
21-50%           1,043,878
51-150%            903,968
151-%                648,891

The method I took was to use asimple table with ranges in it and the below DAX.



Ranking Group = CALCULATE(VALUES('Ranking Buckets'[Rank]), FILTER(ALL('Ranking Buckets'),Sales_Analysis[Sales Ranking] >= 'Ranking Buckets'[Start] && Sales_Analysis[Sales Ranking] <= 'Ranking Buckets'[End]))

In PowerPivot previously this worked  and I could see all ranking groups.  However in BI the behaviour is different it seems, and everything is lumped into the first group.