Bottom 5 customer issue

[marcus_steggall]

Hi team

Quick query, which my eyes cannot see

basically as per attached file, im trying to rank top 5 and bottom 5 customers, within a bar graph.

As you can see from middle table, the top 5 is working perfectly numbers wise.

However, the bottom 5 is nonsense in numbers and i can't see why?

I've done the minus in front of sum, within the calculation (total -Sum)

Any advice would be greatly appreciated.

Thanks

[jaibau1993 · Accepted solution]

Hi!

There are two mistakes in your file:

1) In your "bottom" chart, you use a rank function. This rank function ranks by "minus the rank function in your top chart". The logic is correct but you missed a sign change. You used

IF(

rank(total -SUM({<Year_YYYY={'$(vMaxYear)'},Period_P=,Period_Start={"<=$(vMaxDate)"}>}[AMT R]) 
- 
Sum({$<Year_YYYY={'$(vPriorYear)'},Period_P=,Period_Start={"<=$(vPriorYearDate)"}>}[AMT R]))<=5,


(SUM({<Year_YYYY={'$(vMaxYear)'},Period_P=,Period_Start={"<=$(vMaxDate)"}>}[AMT R])
-
Sum({$<Year_YYYY={'$(vPriorYear)'},Period_P=,Period_Start={"<=$(vPriorYearDate)"}>}[AMT R])))

But it should be

IF(

rank(total -SUM({<Year_YYYY={'$(vMaxYear)'},Period_P=,Period_Start={"<=$(vMaxDate)"}>}[AMT R]) 
+ //It has to be a "+"!!
Sum({$<Year_YYYY={'$(vPriorYear)'},Period_P=,Period_Start={"<=$(vPriorYearDate)"}>}[AMT R]))<=5,


(SUM({<Year_YYYY={'$(vMaxYear)'},Period_P=,Period_Start={"<=$(vMaxDate)"}>}[AMT R])
-
Sum({$<Year_YYYY={'$(vPriorYear)'},Period_P=,Period_Start={"<=$(vPriorYearDate)"}>}[AMT R])))

2) It is not the correct way to shoy top/bottom customers. The right and easiest way is by configuring the "Dimension Limits" tab in your chart properties. 

Please, find the attached file (I think that I explained myself quite bad)

Bests,

Jaime.

[marcus_steggall]

Awesome many thanks,
I swear trying dimension limits, didn't work for me last time hence the rank function!
Appreciate the time and advice Jaime