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
