DAX: Ranking a measure based on a Column

[Anonymous]

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.

[v-yalanwu-msft · Accepted solution]

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.

[amitchandak]

@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

[Anonymous]

@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

[amitchandak]

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

[Anonymous]

@amitchandak below is the expected result that i am looking for.

Please help.

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

[Anonymous]

Thanks for the help @amitchandak 

Please check the question as i have updated the expected result.

But it is not working as expected.

Its returning the ranks for total sales but not as per the Customer groups.

If you see the dax which i tried, giving the ranking for the Tier2 groups properly, but not to the Tier1 groups.

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
    )

Two issues with the above query

1 - It gives the same ranks for tier1 customers - should give the ranks for these customers as per the total sales values in desc.

2 - When i remove the Customergroup column from the visual. ranks are given as per the total sales, but it should break as per the customer group.

Need your help @amitchandak 

Thanks,

Mohan V.

[Anonymous]

@amitchandak @Ashish_Mathur Could you please help me with this.