Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
BruceAnderson
Helper II
Helper II

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

 

Ranking Group2.png

 

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

 

Ranking Group.png

 

 

 

 

1 ACCEPTED SOLUTION

hi,@BruceAnderson

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

before

14.PNG

After

15.PNG

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:

16.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
v-lili6-msft
Community Support
Community Support

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

6.PNG

7.PNG

 

then 

8.PNG

 

here is pbix, please try it.

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

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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"

 

Ranking Result.png

hi,@BruceAnderson

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

before

14.PNG

After

15.PNG

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:

16.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Result No Filter.png

 

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.

 

Result with filter.png

 

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]))

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.

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"

 

 Ranking Result

Many thanks for that Lin!

I will give it a try.

v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

DataSource.png
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.png

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.

 

Group Result.png

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.