I want to make a slicer that is able to manipulate a donut chart, that will display the most repeated values according to the number selected in the slicer.

Say I have this table

Customer
1	Doug
2	Andy
3	Doug
4	Kathy
5	Andy
6	Doug
7	Doug

I want to have a slicer that when on 1 will display Doug on the chart, on 2 will display Doug and Andy, and on 3 will display Doug, Andy, and Kathy.

I have a measurement

TopCustomer = Calculate(
TOPN(
'TOPN Selection'[Number of Customers'],
VALUES('Table'[Customers]),
RANKX( ALL ('Table'[Customers] ), COUNTROWS('Table'),,DESC)
)
)

In this case "TOPN Selection" is a table like this

TOPN Value
1
2
3

And 'TOPN Selection'[Number of Customers] is a measurement

Number of Customers = SELECTEDVALUE(
'TOPN Selection'[TOPN Value]
)

I have my measurement "TopCustomer" set as the value in my donut chart, but when I use the slicer, nothing changes.

I am not sure if my measurements are off, or the way things are connected are off, or what may be the case as dax is a little new to me.

1 Answer

Please test this code:

TopCustomer =
VAR PickFromSlicer = SELECTEDVALUE('TOPN Selection'[TOPN Value])
RETURN
SUMX(
TOPN(
PickFromSlicer,
FILTER(ADDCOLUMNS( VALUES(YourTable[Customer]), "Total",CALCULATE(COUNTROWS(YourTable)), "Rank", RANKX(ALL(YourTable[Customer]),CALCULATE(COUNT(YourTable[Customer]))) ),[Rank] <= PickFromSlicer),[Total],DESC),[Total] )

If we test it on a visual just you described at the end of your question:

Rank_1:

Rank_2

Rank_3

I hope This solves your problem.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.