Forum Discussion
Unwanted summarization affecting rank
I'm trying to produce some tables showing "top 10's" - this didn't work by filtering a visual so I am attempting to use the rankx function to achieve the same. The intention is that I will have 1 table visual showing the data, and a slicer available based on a "category" column - the table should always show the top 10 for whetever is selected in the slicer (including multiple selections or everything).
My data has the following:
Person
Date
Category
Amount.
I have the following 2 measures:
Total Amount = SUMX(PayHistory,PayHistory[AMOUNT])
Rank = RANKX(ALLSELECTED(PayHistory),[Total Amount],,DESC,Skip)
My problem is, illustrated below, 2 payments that are of the same person/date/catgeory/amount - and these 2 aren't being displayed properly (Dan has 2 £4k payments):
Dan's 2 payments aren't being shown - only 1 is, even though in the table visual all columns are set to "Don't summarize". As rank is based on the measure "Total Amount" - then the £8k payments are being ranked #1 whereas in reality, Steve's £5k payment should be #1 and Dan's payment's should be #2 & #3 (well both #2 as the rank is set to "Skip").
How can I get my table visual to show Dan's 2 identical payments on 2 separate rows - for my 2 measures to treat them separately and therefore show correctly.
Here's my sample file .
I have tried adding an index column - but this only fixes my problem if I display the index column in the visual, which I realy don't want to do.
1 Reply
- amitchandakSuper User
Anonymous , add an index column for that and use that in table
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi