Calculated column showing top 1, top 2 value etc. | Forum

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