Log In
Register
Home
Power Query
Excel for Finance
Power BI
Excel Expert
Excel Customer Service
Financial Modelling
September 21, 2018
Offline
I have a measure
Top 5 90+:=CALCULATE( [90+ Debt],TOPN(5,'Consolidated Aging','Consolidated Aging'[Customer Id],desc))
It doesn't total the Top 5? In this it misses the 1st line. If I change the measure to ASC it picks another random 4
I've tried using rankx but that gives me other issues
90+ Days rank based on Debt:=VAR CustomerRank = RANKX(ALLSELECTED('Consolidated Aging'[Customer Id]),[90+ Debt],,ASC,Dense
)
VAR Result =
SUMX(VALUES('Consolidated Aging'[Customer Id]),IF(CustomerRank<=5,[90+ Debt]))
RETURN
Result
How can I fix it?
July 16, 2010
Offline
Hi Alison,
I've moved this topic to the Power Pivot forum.
It's impossible to troubleshoot without seeing your file, or at least a desensitised sample of the data that can be used to reproduce the issues.
I recommend you write the two measures separately first, and then put them in the PivotTable so you can see the output each is returning to verify that first of all the RANKX is returning what you'd expect. From there you can figure out which part of the formula is causing the issue.
If you're still stuck, please provide a sample file that shows the issue and your expected result.
Mynda
1 Guest(s)
