Rankx in the Matrix Hierarchy without no sales rows.

[Young_G_Han]

Hi.

I made a calculation for the rank displayed in a Matrix visualization with the formula below and it seems working.

Formula

Multi Rank =

if(

hasonevalue(Salesman[Salesman]),

RANKX(ALLSELECTED(Salesman[Salesman]),

CALCULATE([Sales], ALLSELECTED(Salesman[Region]))

),

if(

HASONEVALUE(Salesman[region]),

RANKX(ALLSELECTED(Salesman[Region]),

CALCULATE([Sales], ALLSELECTED(Salesman[Company Type]))

)

))

Matrix

Region     Salesman     Rank      Sales Amount

RegionA                          1           xxxxxx

                SalesmanA      1           xxxxxx

                SalesmanB      5           xxxxxx

RegionB                          2           xxxxxx

                SalesmanC      2           xxxxxx

                SalesmanD      6           xxxxxx

                SalesmanE      30          0

However, I need to delete the SalesmanE (Actually, I have many salesmen like E having no sales). So I made a filter for the Matrix, gave a condition as [Sales Amount] >0. When I apply the filter, the Matrix will show a different result as below.

All ranking of regions become '1' and the rank of salesman will be recalculated in each region.

Is there any way, I can keep the first result without the salesmen like the salesmanE?

Please help! 🙏

Matrix

Region     Salesman     Rank      Sales Amount

RegionA                          1           xxxxxx

                SalesmanA      1           xxxxxx

                SalesmanB      2           xxxxxx

RegionB                          1           xxxxxx

                SalesmanC      1           xxxxxx

                SalesmanD      2           xxxxxx

[amitchandak]

@Young_G_Han , best is that you use a visual level filter for sales > 0

or try like, where sales is measure

Multi Rank =
if(
hasonevalue(Salesman[Salesman]),
RANKX(ALLSELECTED(Salesman[Salesman]),
CALCULATE(sumx(filter(values(Salesman[Salesman]), [Sales] >0), [sales]), ALLSELECTED(Salesman[Region]))
),
if(
HASONEVALUE(Salesman[region]),
RANKX(ALLSELECTED(Salesman[Region]),
CALCULATE(sumx(filter(values(Salesman[Company Type]), [Sales] >0), [sales]), ALLSELECTED(Salesman[Company Type]))
)
))

[Young_G_Han]

Thank you for your advice.

I tried your formula, but the result is the same. I still have the 0 sales salesman in the Matrix.

😥