Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.