Hi,
I have a SSAS tabular model of CRM online data and I am trying to use the RANKX function to rank open opportunities for the current quarter by Estimated Base Value and have the function interact with the slicers for Business Line and Region.
My two measures are
Pipeline:=calculate(SUM([Estimated Base]),Opportunity[EstimatedBaseValue] <> blank(),'Estimated Close Date'[Current Quarter]="Yes",Opportunity[Status]="Open")
Rank:=RANKX(ALLSELECTED(Opportunity),[Pipeline])
I am displaying the results in a table in Power BI and everything works fine if I only have the Rank & Estimated Base Value as columns in my table. The problem starts when I add columns to the table.
I want to have the Opportunity Name in the table, but when I add it I get replication of results all ranked equal last. The results repeat by Opportunity Name and then by Estimated Base Value like below
Rank Opp Name Estimated Base Value
1 A £100
2 B £50
3 C £10
4 A £10
4 B £10
4 A £50
4 C £50
4 B £100
4 C £100
If I take Opp name out of the table it behaves how I would expect
Rank Estimated Base Value
1 £100
2 £50
3 £10
Any help would be greatly received
Thanks
