Forum Discussion

ChristianHE's avatar
ChristianHE
Frequent Visitor
5 years ago

Dynamic Top10 list

Hi community

 

I am having a problem and i can't seem to find an answer.

 

I have a dataset with the following columns:

 

Customer, Region, Department, Salesman, Sales last year, Sales this year

 

The user need to be able to filter on: Region, Department, Salesman

 

The result i want is a top10 list of best customers which fit the criteria. For example if they choose the region: "Jylland", and Salesman "Karl", it should give me the 10 best customers in "Jylland" with "Karl". If i choose "Karl" and "Erik" as salesman, i want it to give me the top ten between them.

 

The measure i have tried is this:

 

Stor Top10 =
Var  Rep = VALUES(Kundetabel[Salesman])
Var  Kunde = VALUES(Kundetabel[Customer])
Var  Afdeling = VALUES(Kundetabel[Department])
Var Region = VALUES(Kundetabel[Region)
 
Return
CALCULATE([Sales];
TOPN(10;
CALCULATETABLE(FILTER(all(Kundetabel);
Kundetabel[Department] in Afdeling &&
Kundetabel[Oms År] > 0 )
)
;[Sales)
;Region;Department;Salesman;Customer
)

 
This gives me a top ten for each department i choose, and i cannot get it to accept more Variables in my calculatetable.
 
I have also tried with a RANKX:
 
PotRank =
RANKX(
ALLSELECTED(Kundetabel)
;[Sales];;ASC;Skip)

 

combined with another RANKX

Salg Top10
Var Plads = [PotRank]

return
CALCULATE([Sales];
FILTER(Kundetabel;
Plads<=10))

 

Which gives me the right result, but i cannot remove the blanks in it.

 

RANKX
TopN
with filters