RANKX giving double rank when filter is used

[cbolling]

I have a table in power BI that looks like this:

Customer | Revenue | Rank | Cumulative % of Revenue |

A              | 5,620      | 8       | 13.4%                                |

B              | 5,146      | 10     | 16.0%                                 |
C              | 5,122      | 10     | 16.0%                                |

I have a filter on this visual that says "Revenue is not blank". 

When I clear that filter, I get a bunch of blank entries at the end, but the ranking and cumulative corrects itself.

Here is my Ranking formula:

Ranking = RANKX(ALLSELECTED(Sales[Customer]), [TOTALREVENUE],,DESC,Dense)

Any idea on why this filter is throwing off my rank and cumulative %? And why are blank values coming up if there is no revenue.

[amitchandak]

@cbolling , This Rank formula seems fine. Are having Revenue formula like

revenue = sum(Table[sales])+0 // of handled blank

Also Cumalative should use allselcted like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[cbolling]

I have fixed the previous problem from pulling customer from the customer table, rather than our sales table.

Now there is a new problem. This file seems to be taking up too much computing power as this customer table is rather large, and all the measures are built off of each other.

TOTALREVENUE = SUM(FactSales[revenue])

Rank = RANKX(ALLSELECTED(DimCustomer[Customer]), [TOTALREVENUE],,Desc,Skip)

CumulatedRevenue =

VAR CurrentRank = [Rank]

RETURN

SUMX(FILTER(ALLSELECTED(DimCustomer[Customer]),

[Rank]<= CurrentRank), [TOTALREVENUE])

CumulativeRevenue% = [CumulatedRevenue]/CALCULATE([TOTALREVENUE], ALLSELECTED(DimCustomer[Customer]))

CLASS =

VAR TopPercentage = 0.8

VAR MiddlePercentage = 0.9

VAR TotalRevenue = CALCULATE([TOTALREVENUE],ALLSELECTED())

VAR TopRankNumber = MAXX(FILTER(ALLSELECTED(DimCustomer[Customer]), [CumulatedRevenue]<=TotalRevenue*TopPercentage), [Rank])

VAR MiddleRankNumber = MAXX(FILTER(ALLSELECTED(DimCustomer[Customer]), [CumulatedRevenue]<= TotalRevenue*MiddlePercentage), [Rank])

RETURN

IF(

OR(ISBLANK([Rank]), ISBLANK([TOTALREVENUE])), BLANK(),

IF([Rank] <= TopRankNumber, "Top", IF([Rank] <= MiddleRankNumber, "Middle", "Bottom")))

These are the mesures that the table uses, and they all build on each other.

[cbolling]

THis is the error message I am getting when I use the customer table.

[Anonymous]

Please post the cardinalities of the tables involved.

[cbolling]

The relationship from FactSales to DimCustomer is Many to One

[cbolling]

Also, when I use "Skip" instead of "dense" the problem is still there