Visual filter using TOP N

[admin_xlsior]

Hi guys,

Need advice how to use TOP N feature in Visual level filters. 

If lets say I have this kind of data :

Customer Group	Customer	Sales Amount
Group A	A001	12
Group A	A002	77
Group A	A003	68
Group A	A004	35
Group B	B001	11
Group B	B002	16
Group B	B003	71
Group B	B004	37
Group B	B005	93
Group C	C001	16
Group C	C002	81
Group C	C003	86
Group C	C004	53

If we calculate manually, the total per Customer group, we get :

Group A Total	192
Group B Total	228
Group C Total	236

So in Power BI, with the original data, I'm using Funnel visual to display Top 2 of it, but I need to put the Customer Group then Customer it self (in its setting - Group)

The question is how to set the TOP 2 in each of the group (TOP 2 Customer group and TOP 2 Customer). If I'm setting Visual filter - Customer Group with TOP N and set to 2, after I drill down to the Customers, obviusly the Customers will not display TOP 2 only but more than that. And if I put filter on both Customer Group and Customers, the setting will give me some warning and the input field is disabled, like this :

Can we set both as TOP 2 ?

Based on the original data, I would like my Funnel visual to show like this :

TOP 2 Group

Top 2 Customer Group	Sales Amount
Group C	236
Group B	228

TOP 2 Customers

Top 2 Customer	Sales Amount
Customer B005	93
Customer C003	86

Thanks,

[v-xuding-msft]

Hi @admin_xlsior ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Best Regards,

Xue

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

[v-xuding-msft]

Hi @admin_xlsior ,

For a workaround, you could create a measure to get top 2 of customers.

Sum Sales Amount = CALCULATE(SUM('Table'[Sales Amount]),ALLEXCEPT('Table','Table'[Customer Group   ]))

TOP2 Customers = CALCULATE(MAX('Table'[Customer]),TOPN(2,ALLEXCEPT('Table','Table'[Customer Group   ],'Table'[Customer]),'Table'[Sales Amount],DESC))

Best Regards,

Xue

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

[admin_xlsior]

Hi, Thanks, but if I combine Customer group + Customer then TOP 2 based on Sales Amount, it will be the same as TOP 2 of customers, am I right ? 

There 2 different result on my original data, By Group, the no 1 is Group C, whilst By Customer, no.1 is B005  (which is belong to Group B)

Thanks,

[Gordonlilj]

Hi,

You could try and create a calculated column that combines group name and customer like this for example.

CalculatedColumn = 'Table'[Customer Group] & " " & 'Table'[Customer]

Then use the new column just as an extra visual filter and not in the actual visualisation

Doing this gives me the correct result based on the data and example you provided