Finding the share of orders containing only dynamic top n items

[iggyvic]

Hi,

I have a table containing the following columns:

Document No_	Posting Date	Line No_	Sell-to Customer No_	No_	Location Code	Quantity
1693097	2020-01-11	10000	226789	62173	5	1
1692262	2020-01-11	10000	158058	69164	2	1
1692263	2020-01-11	10000	245064	97233	85	1
1693098	2020-01-11	10000	249473	67020	8	1
1693096	2020-01-11	10000	241428	40680	4	1
1693098	2020-01-11	20000	249473	68084	8	1
1692263	2020-01-11	20000	245064	96894	85	1
1693098	2020-01-11	30000	249473	68082	8	1
1692263	2020-01-11	30000	245064	99269	85	1
1692262	2020-01-11	30000	158058	42503	2	1
1692262	2020-01-11	40000	158058	99396	2	1
1692262	2020-01-11	60000	158058	99486	2	1
1692262	2020-01-11	70000	158058	97711	2	1
1692262	2020-01-11	80000	158058	90177	2	1
1692262	2020-01-11	50000	158058	99401	2	2
1692262	2020-01-11	100000	158058	32313	2	2
1692262	2020-01-11	90000	158058	32314	2	3
1692262	2020-01-11	20000	158058	32755	2	2

I found the following DAX-code online and managed to rank the items (No_) by distinct count of order (Document No_)

Rank =
VAR __currentCategory =
    MAX ( 'Sales Invoice Line'[No_] )
VAR __tmpTable =
    ALL ( 'Sales Invoice Line'[No_] )
VAR __tmpTable1 =
    SUMMARIZE (
        __tmpTable,
        'Sales Invoice Line'[No_],
        "__Count", COUNT ( 'Sales Invoice Line'[No_] )
    )
VAR __tmpTable2 =
    ADDCOLUMNS ( __tmpTable1, "__Rank", RANKX ( __tmpTable1, [__Count] ) )
RETURN
    MAXX (
        FILTER ( __tmpTable2, 'Sales Invoice Line'[No_] = __currentCategory ),
        [__Rank]
    )

The rank is updated based on a date slicer (Posting Date).

I would like to expand on this solution to find the share of:

1. Orders containing only one of any top n items, and

2. Orders containing several rows but only any top n items

and have the date slicers controlling these results as well. 

I'm thinking something along the lines of:

Date Slicer: 2021-01-01 -> 2021-12-31

Top N: 1000

Item Rank	No_	Distinct count of Document No_	Count of 1-line order containing item
1	62173	2647	543
2	69164	2100	999
3	97233	1900	1234
...	...
1000	67020	23	23

And then a separate measure

% of distinct count of orders that only contain top N items

I tried searching but couldn't find a way to filter on the dynamic top items among the orders. Does anyone know where I could start looking or how this could be achieved? 

[iggyvic]

Hey and thanks for getting back to me! 

Yes, the slicer should be used to control the data included in the calculations. Say if you select January 2022, you might get a different top (more heating related items) list of items compared to June 2021 (more barbecue related items), depending on what seasonally sells. 

Now of course the limited data set I pasted wasn't especially useful (sorry!) but I've taken a week's worth of data and anonymized it a bit in this .pbix: Sales.pbix

There it should say that:

1. 62279, 99180 and 61985 are the top (for example three) items that week (because they are the No_ contained in the largest number of distinct Document No_)

2. And that 371 of 543 of the Document No_ contain only at least one of these three top items

Hope that makes it clearer? Otherwise I'm happy to clarify further!

[v-xiaotang]

Hi @iggyvic 

"1. Orders containing only one of any top n items, and

2. Orders containing several rows but only any top n items"

-  Do you mean you want to use a slicer to select the modes above? And could you share the expected results according to your data? Thanks. (I'm just want to know how it displays based on your data.

Best Regards,

Community Support Team _Tang

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