Hi, I'm stuck with the Rank measure issues on the client visual -
Here are the components on my report -
Tables -
DimDate - DateKey, Date
DimLocation - LocationKey, Region
DimClient - ClientKey, ClientID, ClientType, ClientName
DimRLSClient - ClientKey, UserName
FactSales - DateKey, ClientKey, LocationKey, Amount
Each client sees their single client key on DimRLSClient tables when login with their UserName
Table visuals -
Client Snapshot - ClientName, ClientID, AmountRank, PercentileRank, TotalCount,TotalAmount, DimRLSClient[ClientKey]
Marketplace Summary - ClientType, ClientID, AmountRank, PercentileRank, TotalCount, TotalAmount
Slicers -
Date Range
Region
ClientType (e.g. Retail, WholeSale, Unclassified)
Filter on Client Snapshot visual - DimRLSClient.ClientKey > 0 (so on Client Snapshot visual, it shows only one record)
Measures -
AmountRank = RANKX(ALLSELECTED('DimClient'), CALCULATE(SUM('FactSales'[Amount])),, DESC, Skip)
NumberOfRankings = MAXX(ALLSELECTED('DimClient'),'FactSales'[AmountRank])
PercentileRank = (NumberOfRankings -[AmountRank]) / (NumberOfRankings - 1) * 100
On the Marketplace Summary visual, the AmountRank and PercentileRank look good and respond to all slicers correctly.
But on the Client Snapshot visual - AmountRank is always 1, and PercentileRank is NaN. I guess I probably need to apply DimRLSClient.ClientKey filter after the Ranks are calculated. I just don't know how to do it. Can anyone please offer some solutions? Any help would be much appreciated!
Thanks
