Forum Discussion
DAX: Ranking a measure based on a Column
Hello All,
I am trying to create ranking of sales by customers and customer groups.
Now these customers are been grouped as tier 1 and tier2.
But in visualization I am using only Customer column but not Customergroup.
Data looks like as below
Power Query:-
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZQ7csMwDETvopozJkBSIOtcIZ3HZQq3vn8RfeBoyZWVyh6/pfSMhXS/T18yhUlu6aZRdf0acm7L5/L7I/xhiQe3WZgb8DwT13zwapl4EjhfC3Ad9ZqlFSti1GvxhINeLZX4oachNj5/6K1cgKdeT4Ok7fIJ8aG38KrMDXgR4qin0oijXi4ZeB71yn48I0a9OSlzo3I6juV6Ox3Hcr0d52Us18spiFHPy+m5UTkdx+l5Ox3H6Xk7zmcqdyvn+/nzUkyg4d4PR2ysiCLoWc6vgqp7UxCx864pwXVzhBqnCJfOKle2dbSlqVSypT9UabYf7nM52/q/bRtt90WjRPekb7vGERvXjSLdxo4r18iWVk7iqPspgr5UwDtjF7PzDBrT8N469I73zOMX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Begin of Week" = _t, Sales = _t, #"Customer Group" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Sales", Int64.Type}, {"Customer Group", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Begin of Week", type date}}, "en-ZA"),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type with Locale", "Tier1/2", each if [Customer Group] <> "Tier2" then "Tier 1" else "Tier 2")
in
#"Added Conditional Column"
I have created table visual as below and I have timeslicer which filters the dates and customer hierarchy slicer with customer and customergroup in it.
I have created ranking measure using below dax
Total Sales = CALCULATE(SUM('Table'[Sales]),ALLSELECTED('Table'[Begin of Week]))
Top 5 Customers =
RANKX(
ALL('Table'[Customer]),
CALCULATE(
'Table'[Total Sales],
ALLEXCEPT('Table','Table'[Customer],'Table'[Customer Group])
)
,,
DESC
)
Above measure giving me the result as below
The ranking for Tier2 customers it is giving right output, but the other customers the ranking it returns as 1 only.
Output that I am expecting as
Note:- In final visualization, I will not be using that Customer group. So ranking should work though i do not use that column in visual.
Expected result.
| Begin of Week | Customer | Total Sales | Top 5 | Expected Ranking | Rule |
| 03-01-2022 | C1 | 8615 | 10 | 5 | Part of Tier1 customegroup |
| 10-01-2022 | C1 | 8615 | 10 | 5 | Part of Tier1 customegroup |
| 17-01-2022 | C1 | 8615 | 10 | 5 | Part of Tier1 customegroup |
| 24-01-2022 | C1 | 8615 | 10 | 5 | Part of Tier1 customegroup |
| 31-01-2022 | C1 | 8615 | 10 | 5 | Part of Tier1 customegroup |
| 03-01-2022 | C2 | 9918 | 9 | 4 | Part of Tier1 customegroup |
| 10-01-2022 | C2 | 9918 | 9 | 4 | Part of Tier1 customegroup |
| 17-01-2022 | C2 | 9918 | 9 | 4 | Part of Tier1 customegroup |
| 24-01-2022 | C2 | 9918 | 9 | 4 | Part of Tier1 customegroup |
| 31-01-2022 | C2 | 9918 | 9 | 4 | Part of Tier1 customegroup |
| 03-01-2022 | C5 | 10362 | 8 | 3 | Part of Tier1 customegroup |
| 10-01-2022 | C5 | 10362 | 8 | 3 | Part of Tier1 customegroup |
| 17-01-2022 | C5 | 10362 | 8 | 3 | Part of Tier1 customegroup |
| 24-01-2022 | C5 | 10362 | 8 | 3 | Part of Tier1 customegroup |
| 31-01-2022 | C5 | 10362 | 8 | 3 | Part of Tier1 customegroup |
| 03-01-2022 | C9 | 10781 | 7 | 5 | Part of Tier2 customegroup |
| 10-01-2022 | C9 | 10781 | 7 | 5 | Part of Tier2 customegroup |
| 17-01-2022 | C9 | 10781 | 7 | 5 | Part of Tier2 customegroup |
| 24-01-2022 | C9 | 10781 | 7 | 5 | Part of Tier2 customegroup |
| 31-01-2022 | C9 | 10781 | 7 | 5 | Part of Tier2 customegroup |
| 03-01-2022 | C4 | 10812 | 6 | 2 | Part of Tier1 customegroup |
| 10-01-2022 | C4 | 10812 | 6 | 2 | Part of Tier1 customegroup |
| 17-01-2022 | C4 | 10812 | 6 | 2 | Part of Tier1 customegroup |
| 24-01-2022 | C4 | 10812 | 6 | 2 | Part of Tier1 customegroup |
| 31-01-2022 | C4 | 10812 | 6 | 2 | Part of Tier1 customegroup |
| 03-01-2022 | C3 | 11144 | 5 | 1 | Part of Tier1 customegroup |
| 10-01-2022 | C3 | 11144 | 5 | 1 | Part of Tier1 customegroup |
| 17-01-2022 | C3 | 11144 | 5 | 1 | Part of Tier1 customegroup |
| 24-01-2022 | C3 | 11144 | 5 | 1 | Part of Tier1 customegroup |
| 31-01-2022 | C3 | 11144 | 5 | 1 | Part of Tier1 customegroup |
| 03-01-2022 | C7 | 11541 | 4 | 4 | Part of Tier2 customegroup |
| 10-01-2022 | C7 | 11541 | 4 | 4 | Part of Tier2 customegroup |
| 17-01-2022 | C7 | 11541 | 4 | 4 | Part of Tier2 customegroup |
| 24-01-2022 | C7 | 11541 | 4 | 4 | Part of Tier2 customegroup |
| 31-01-2022 | C7 | 11541 | 4 | 4 | Part of Tier2 customegroup |
| 03-01-2022 | C10 | 11609 | 3 | 3 | Part of Tier2 customegroup |
| 10-01-2022 | C10 | 11609 | 3 | 3 | Part of Tier2 customegroup |
| 17-01-2022 | C10 | 11609 | 3 | 3 | Part of Tier2 customegroup |
| 24-01-2022 | C10 | 11609 | 3 | 3 | Part of Tier2 customegroup |
| 31-01-2022 | C10 | 11609 | 3 | 3 | Part of Tier2 customegroup |
| 03-01-2022 | C6 | 11975 | 2 | 2 | Part of Tier2 customegroup |
| 10-01-2022 | C6 | 11975 | 2 | 2 | Part of Tier2 customegroup |
| 17-01-2022 | C6 | 11975 | 2 | 2 | Part of Tier2 customegroup |
| 24-01-2022 | C6 | 11975 | 2 | 2 | Part of Tier2 customegroup |
| 31-01-2022 | C6 | 11975 | 2 | 2 | Part of Tier2 customegroup |
| 03-01-2022 | C8 | 12756 | 1 | 1 | Part of Tier2 customegroup |
| 10-01-2022 | C8 | 12756 | 1 | 1 | Part of Tier2 customegroup |
| 17-01-2022 | C8 | 12756 | 1 | 1 | Part of Tier2 customegroup |
| 24-01-2022 | C8 | 12756 | 1 | 1 | Part of Tier2 customegroup |
| 31-01-2022 | C8 | 12756 | 1 | 1 | Part of Tier2 customegroup |
Anyone please suggest me.
Thanks,
Mohan V.
Hi, Anonymous ;
Try it.
Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Customer Group]=MAX('Table'[Customer Group])&&[Customer]=MAX('Table'[Customer])))Top 5 Customers = RANKX( FILTER( ALL('Table'),[Tier1/2]=MAX('Table'[Tier1/2])), CALCULATE( [Total Sales]) ,, DESC,Dense)The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Anonymous , You are using date in visual, and rank will distribute inside date.
In your, measure remove the date or use that in rank too
Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA
amitchandak The dates should be there in the visual.
But the ranking should be done by the total sales of each customer for all dates.
Please find the Pbix file for your refernce.
https://1drv.ms/u/s!AmauTLNmHKexhGcs7UQE-SoJtnTZ
Anonymous Try if the two measures can help
M1= CALCULATE(SUM('Table'[Sales]),,filter(allseleced('Table'),'Table'[Customer] = max('Table'[Customer]) && 'Table'[Customer Group] =max('Table'[Customer Group] )))
Top 5 Customers =
RANKX(
ALL('Table'[Customer],'Table'[Customer Group], 'Table'[Date]),
[M1]
,,
DESC
)
Hi, Anonymous ;
Try it.
Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Customer Group]=MAX('Table'[Customer Group])&&[Customer]=MAX('Table'[Customer])))Top 5 Customers = RANKX( FILTER( ALL('Table'),[Tier1/2]=MAX('Table'[Tier1/2])), CALCULATE( [Total Sales]) ,, DESC,Dense)The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
