Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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?
Solved! Go to 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
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
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
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]))
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"
Many thanks for that Lin!
I will give it a try.
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.
User | Count |
---|---|
94 | |
92 | |
84 | |
83 | |
49 |
User | Count |
---|---|
145 | |
142 | |
111 | |
71 | |
55 |