TopN while using duplicate values

[SK01]

Hi everyone,

I need your help with the following problem. I need to be able to display the topN cargo ID based on their cycle time. 

I am trying to use TOPN to display values based on the lowest cycle time. So for example, when you input a value into a slider slicer, that same value should correspond to the amount of units shown. 

The desired result would be something similar to this. 'Units' is calculated by using the COUNT formula on the cargo id column.

The concat column might not be necessary fyi, but I included it just in case.

Below you can find the dummy data:

Cargo ID	Cycle Time	Location	Concat
VIN1	500	CKX	500VIN1
VIN2	500	CKX	500VIN2
VIN3	500	CKX	500VIN3
VIN4	500	CKX	500VIN4
VIN5	500	CKX	500VIN5
VIN6	500	CKX	500VIN6
VIN7	500	CKX	500VIN7
VIN8	500	CKX	500VIN8
VIN9	500	CKX	500VIN9
VIN10	500	CKX	500VIN10
VIN11	500	CKX	500VIN11
VIN12	500	CKX	500VIN12
VIN13	500	CKX	500VIN13
VIN14	500	CKX	500VIN14
VIN15	500	CKX	500VIN15
VIN16	500	CKX	500VIN16
VIN17	500	CKX	500VIN17
VIN18	500	CKX	500VIN18
VIN19	500	CKX	500VIN19
VIN20	500	CKX	500VIN20
VIN21	500	CKX	500VIN21
VIN22	500	CKX	500VIN22
VIN23	500	CKX	500VIN23
VIN24	500	CKX	500VIN24
VIN25	500	CKX	500VIN25
VIN26	500	CKX	500VIN26
VIN27	550	P9	550VIN27
VIN28	550	P9	550VIN28
VIN29	550	P9	550VIN29
VIN30	550	P9	550VIN30
VIN31	550	P9	550VIN31
VIN32	550	P9	550VIN32
VIN33	550	P9	550VIN33
VIN34	550	P9	550VIN34
VIN35	550	P9	550VIN35
VIN36	550	P9	550VIN36
VIN37	550	P9	550VIN37
VIN38	550	P9	550VIN38
VIN39	550	P9	550VIN39
VIN40	550	P9	550VIN40
VIN41	550	P9	550VIN41
VIN42	550	P9	550VIN42
VIN43	550	P9	550VIN43
VIN44	550	P9	550VIN44
VIN45	550	P9	550VIN45
VIN46	550	P9	550VIN46
VIN47	550	P9	550VIN47
VIN48	550	P9	550VIN48
VIN49	550	P9	550VIN49
VIN50	550	P9	550VIN50
VIN51	550	P9	550VIN51
VIN52	550	P9	550VIN52
VIN53	550	P9	550VIN53
VIN54	550	P9	550VIN54
VIN55	550	P9	550VIN55
VIN56	600	CKU	600VIN56
VIN57	600	CKU	600VIN57
VIN58	600	CKU	600VIN58
VIN59	600	CKU	600VIN59
VIN60	600	CKU	600VIN60
VIN61	600	CKU	600VIN61
VIN62	600	CKU	600VIN62
VIN63	600	CKU	600VIN63
VIN64	600	CKU	600VIN64
VIN65	600	CKU	600VIN65
VIN66	600	CKU	600VIN66
VIN67	600	CKU	600VIN67
VIN68	600	CKU	600VIN68
VIN69	600	CKU	600VIN69
VIN70	600	CKU	600VIN70
VIN71	600	CKU	600VIN71
VIN72	600	CKU	600VIN72
VIN73	600	CKU	600VIN73
VIN74	600	CKU	600VIN74
VIN75	600	CKU	600VIN75
VIN76	600	CKU	600VIN76
VIN77	600	CKU	600VIN77
VIN78	600	CKU	600VIN78
VIN79	600	CKU	600VIN79
VIN80	600	CKU	600VIN80
VIN81	600	CKU	600VIN81
VIN82	600	CKU	600VIN82
VIN83	650	P26	650VIN83
VIN84	650	P26	650VIN84
VIN85	650	P26	650VIN85
VIN86	650	P26	650VIN86
VIN87	650	P26	650VIN87
VIN88	650	P26	650VIN88
VIN89	650	P26	650VIN89
VIN90	650	P26	650VIN90
VIN91	650	P26	650VIN91
VIN92	650	P26	650VIN92
VIN93	650	P26	650VIN93
VIN94	650	P26	650VIN94
VIN95	650	P26	650VIN95
VIN96	650	P26	650VIN96
VIN97	650	P26	650VIN97
VIN98	650	P26	650VIN98
VIN99	650	P26	650VIN99
VIN100	650	P26	650VIN100

[SK01 · Accepted solution]

@v-xinruzhu-msft I have found a solution.

For anyone interested, here is the measure that uses a slicer to appoint the Top N.

Measure =

VAR a =

    CALCULATETABLE(

        VALUES(

            'Table'[Cargo ID]),

                TOPN(

                    SELECTEDVALUE(

                        Slicer[Slicer]),

                    ALLSELECTED(

                        'Table'),

                        'Table'[RND Time],

                ASC)

    )

VAR selectedCargoID = SELECTEDVALUE ( 'Table'[Cargo ID] )

RETURN

    IF ( selectedCargoID IN b, 1, 0 )

[SK01]

Hey thank you for replying.

This is not exactly the solution I am looking for. Top N should correlate to the amount of units returned; right now it returns 4 values at a maximum.

The desired result would be if Top N is set to 15 to return 15 units based on their lowest times.

I am currently trying something out using AVERAGE('Table'[Cycle Time])+RAND()/100, to create unique time values so that it can be properly displayed. However it doesn't interact well with your measure.

Example:

All possible units.

Filter:

Result:

Note that I am using a different dataset to illustrate.

Can you help me out further?

[SK01]

Just to clarify, the example is only to showcase how I'd like the measure to interact with the data in question, nothing more.

[v-xinruzhu-msft]

Hi @SK01 

You can create a measure

Measure 2 = var a=SUMMARIZE(ALLSELECTED('Table'),'Table'[Location],"MinID",MIN('Table'[Cargo ID]),"Mincycletime",MIN('Table'[Cycle Time]))
var b=TOPN(3,a,[Mincycletime],ASC)
var c=SELECTCOLUMNS(b,"minid",[MinID])
return IF(SELECTEDVALUE('Table'[Cargo ID]) in c,1,0)

Then put the measure to the visual filter

Output

Best Regards!

Yolo Zhu

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