TOP N issue

[Jlbaenlo]

Hi, I have a visual "Table like" containing 

Product-Country, Sales, Switch, KPI1 (all those fields are coming from 1 table called SFA_BIAS)

Siwtch is a value = -1 or 1

and 1 filter applied on the visual on Switch (et's say 1) 

When I apply the TOPN 10 on Product-Country field based on Sales, I would expect to get the Top 10 Product-country based on Sales having Switch  = 1

the table shows no records, as if the TOPN 10 would apply on the full data behind (as the top 10 Product-Country sales without filtering have a switch = -1)

I have also tried with a Rank

RankSales = RANKX(

ALLSELECTED(SFA_BIAS),

CALCULATE(SUM(SFA_BIAS[SALES]))

)

The RankSales applies well to my selection

but when I filter on RankSales <=10 the table does not show me records

Could you please help me to understand what I am doing wrong,

Many thanks for your help,

JL

[Anonymous · Accepted solution]

Hi @Jlbaenlo ,

My sample data is this.

Product-CountrySalesSwitchKPI1

JP	1	1	1
JP	10	1	2
CN	3	1	3
CN	4	1	4
USA	12	1	5
USA	15	1	6
UK	7	1	7
UK	8	1	8
JP	9	1	9
JP	4	1	10
CN	1	1	1
CN	16	1	2
USA	3	1	3
USA	15	1	4
UK	18	1	5
UK	9	1	6
UK	5	-1	7
JP	6	-1	8
JP	1	-1	9
CN	2	-1	10
CN	3	-1	1
USA	4	-1	2
USA	8	-1	3
UK	8	-1	1
UK	7	-1	2
JP	2	-1	3
JP	1	-1	4
CN	3	-1	5
CN	6	-1	6
USA	5	-1	7
USA	1	-1	8
UK	9	-1	9
UK	9	-1	10

You could try to create a measure.This measure is to sort switch=1 and switch=-1 respectively.

Measure =
VAR ran =
    RANKX (
        FILTER ( ALL ( 'SFA_BIAS' ), 'SFA_BIAS'[Switch] = MAX ( 'SFA_BIAS'[Switch] ) ),
        CALCULATE ( MAX ( 'SFA_BIAS'[Sales] ) ),
        ,
        ASC,
        SKIP
    )
RETURN
    IF ( ran <= 10, 1, 0 )

Put the measure into Filters and set it.

The result is this.

You can check more details form here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Jlbaenlo - Sorry, having trouble following, can you post sample data as text and expected output?