Forum Discussion
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]))
However I only see the first row, I am unable to get the other groups. Would anyone have any advice?
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
9 Replies
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
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.
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
Many thanks for that Lin!
I will give it a try.
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"
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"
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
