Problems with Rank when has equal values or null

[rcandeo]

!I have an advanced expression to calculate Rank that brings aleatory values when results are equal or zero or null.

The problem is that dependind on the way that the result is visualized by the user, the result is different.

Look at my example attached. It has two differente ways to calculate, one with texts and beside one with table. Depending of the variable, the result will be different in the both ways.

Just open the application, select for example: Irqbsa DZB and you see that some calculations are differente, like Treaty

[sunny_talwar]

Try this:

Pick(Dim,

Round(Rank(If(Sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} $(vCurrency)) <> 0,

Sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} $(vCurrency))),1,1) * Avg(1)),

Round(Rank(Sum({1<[Reporting Calendar Year]={'$(vYearBefore)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} $(vCurrency))) * Avg(1)

-

Rank(Sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} $(vCurrency))) * Avg(1)),

Num(Sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} $(vCurrency)) * Avg(1)

/

Sum({1<[Reporting Calendar Year]={'$(vYearBefore)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} $(vCurrency)) * Avg(1)

-1,'+ #,##0.00%;(#,##0.00)%'))

Did this for Aviation only, you will probably need to do this for all expressions

[rcandeo]

thank you again so much