Andy has attended:
Introduction to Management course
Calculated column showing top 1, top 2 value etc.
Hello,
I'd like to display a visual showing the highest and second highest (and so on) ID per day based on sales. I don't want this to interfere with other filters on the same visual so I'd like to add calculated columns with this data.
Example of my table below; what I'd like is Top1 and Top2 columns showing the highest and second highest IDs per day based on sales.
Date Sales ID Top 1 Top 2
10/01/2020 10 A D A
10/01/2020 5 B D A
10/01/2020 1 C D A
10/01/2020 25 D D A
11/01/2020 20 A B A
11/01/2020 45 B B A
11/01/2020 6 C B A
11/01/2020 12 D B A
12/01/2020 1 A C B
12/01/2020 15 B C B
12/01/2020 20 C C B
12/01/2020 3 D C B
Thank you
A
RE: Calculated column showing top 1, top 2 value etc.
Hi Andy,
Thank you for the forum question.
I am not sure I understand what you want, but I hope this can help you to get what you want.
If you create a calculated column with the DAX below, you get each day ranked by sales. If you filter by the column, a visual can display the ID and the sales for Top 1 or Top 2 or Top..........
Rank all rows as Column per day =
RANKX(
FILTER(
'Table1',
'Table1'[date]=EARLIER(Table1[Date])
),
'Table1'[Sales]
)
If you want to display the Top 1 ID in a new column:
Top 1 ID = if(Table1[Rank all rows as Column per day]=1,Table1[ID])
If you want to display the Top 2 ID in a new column:
Top 1 ID = if(Table1[Rank all rows as Column per day]=1,Table1[ID])
But I cannot find a way of displaying the ID in each row.
If you want to ignore filtering in a visual, you will need to tell Power Bi which filters you want to ignore by using the All function. I do not know how you have filtered your visuals but if you have filtered the visual by "Rank all rows as Column per day" but still want to see the sale for the rest of the IDs you will need a measure:
Sales All=Calculate(sum(Sales),All([Rank all rows as Column per day])
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
