topic Top N in table with multiple columns in Developer

Top N in table with multiple columns

manojk_pbi — Fri, 04 Aug 2023 07:42:24 GMT

Hi Friends,

I am looking for a suggestion or solution on how to create a table with Top N Customers on sales with other details of customer in the table. Top N can be on entire data or it can be on slicer on ProductType

Appreciate any suggestions here.

Below is the sample data

ID	ProductType	Trend	Title	State	Responsible	Exposure
39428	Product1	Stable	Title1	Closed	User1	20
162792	Product1	Increasing	Title2	New	User2	16
173226	Product1	Decreasing	Title3	Resolved	User3	16
65706	Product1	Stable	Title4	Closed	User4	16
86525	Product2	Stable	Title5	Closed	User5	16
134931	Product2	Stable	Title6	Closed	User6	15
27591	Product2	Stable	Title7	Closed	User7	15
27593	Product2	Decreasing	Title8	Closed	User8	15
38499	Product2	Decreasing	Title9	Closed	User9	15
112655	Product2	Stable	Title10	Closed	User10	12
129003	Product1	Stable	Title11	Mitigated	User11	12
135077	Product1	Decreasing	Title12	Mitigated	User12	12
138692	Product1	Stable	Title13	Closed	User13	12
139250	Product1	Stable	Title14	Closed	User14	12
181593	Product1	Stable	Title15	New	User15	12
27707	Product3	Increasing	Title16	Closed	User16	12
29673	Product3	Decreasing	Title17	Closed	User17	12
42179	Product3	Stable	Title18	Closed	User18	12
49670	Product3	Stable	Title19	Closed	User19	12
59744	Product3	Stable	Title20	Mitigated	User20	12
61017	Product1	Stable	Title21	Closed	User21	12
61018	Product1	Stable	Title22	Closed	User22	12
69808	Product2	Decreasing	Title23	Closed	User23	12
162795	Product2	Increasing	Title24	New	User24	10
27575	Product2	Stable	Title25	Mitigated	User25	10
36986	Product2	Stable	Title26	Closed	User26	10
40810	Product3	Stable	Title27	Active	User27	10
105224	Product3	Decreasing	Title28	Mitigated	User28	9
132406	Product1	Stable	Title29	Active	User29	9
27595	Product3	Stable	Title30	Mitigated	User30	9
40809	Product1	Stable	Title31	Mitigated	User31	9
100761	Product1	Stable	Title32	Closed	User32	6
112651	Product2	Decreasing	Title33	Closed	User33	6
114514	Product2	Stable	Title34	Mitigated	User34	6
129010	Product2	Stable	Title35	Mitigated	User35	6
135246	Product1	Stable	Title36	Closed	User36	6
139228	Product2	Stable	Title37	New	User37	6
27569	Product2	Stable	Title38	Closed	User38	6
27572	Product2	Decreasing	Title39	Closed	User39	6
27574	Product1	Stable	Title40	Closed	User40	6
27580	Product2	Stable	Title41	Mitigated	User41	6
27588	Product2	Stable	Title42	Closed	User42	6
27594	Product2	Stable	Title43	Closed	User43	6
27599	Product1	Stable	Title44	Closed	User44	6
33949	Product2	Decreasing	Title45	Active	User45	6
38486	Product2	Stable	Title46	Active	User46	6
38491	Product2	Decreasing	Title47	Closed	User47	6
61670	Product3	Stable	Title48	Closed	User48	5
61671	Product1	Stable	Title49	Closed	User49	5
86524	Product1	Stable	Title50	Closed	User50	5

Thanks,

Re: Top N in table with multiple columns

Anonymous — Mon, 07 Aug 2023 07:22:51 GMT

Hi manojk_pbi ,

I suggest you to create a number range slicer with what is parameter function.

Then create a DimProductType table for slicer.

Relationship:

Measure:

Rank = VAR _SELECTTOPN = SELECTEDVALUE ( 'Top N'[Top N] ) VAR _RANK = IF ( ISFILTERED ( DimProduct[ProductType] ), RANKX ( ALLEXCEPT ( 'Table', 'Table'[ProductType] ), CALCULATE ( SUM ( 'Table'[Exposure] ) ) ), RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Exposure] ) ),, DESC, DENSE ) ) RETURN IF ( ISFILTERED ( 'Top N'[Top N] ), IF ( _RANK <= _SELECTTOPN, 1, 0 ), 1 )

Add this measure into visual level filter and set it to show items when value = 1.

Best Regards,
Rico Zhou

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

Re: Top N in table with multiple columns

manojk_pbi — Tue, 08 Aug 2023 05:38:53 GMT

Thanks v-rzhou-msft, the solution is close to my requirement. One thing i noticed is eventhough we select Top 2 i could see 4 records with Product1 selected. Why is so, how should we restrict to show only 2 ?

Thanks

Re: Top N in table with multiple columns

Anonymous — Tue, 08 Aug 2023 05:49:25 GMT

Hi manojk_pbi ,

The second value is 16, however there are three data as 16 in Product 1, all of them will be marked as second one.

If you want to sort these three data with same value 16, you need a new condtion like title sort or others. Please tell me more details.

Best Regards,
Rico Zhou

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

Re: Top N in table with multiple columns

manojk_pbi — Tue, 08 Aug 2023 05:56:50 GMT

Hi Rico Zhou, Thanks for quick reply. Do we have option to pick always top N irrespective of repeated value with out any condition ? When I select Top 2 or 3 the result remains same for Product1.

Re: Top N in table with multiple columns

Anonymous — Tue, 08 Aug 2023 06:08:02 GMT

Hi manojk_pbi ,

As above I add a rank in my calculation, so if you want to show TOP N in another way, we need to add some condtions in my code to create new rank.

You can show me a screenshot with the result you want as Product Top2 and Top3, I will try to find the condtion to help you solve your issue.

Best Regards,
Rico Zhou

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

Re: Top N in table with multiple columns

manojk_pbi — Tue, 08 Aug 2023 08:00:18 GMT

HI Rico Zhou, I created new column with RAND() function and used it in RANK Measure. It is working as expected. Thanks for the solution and advise.