Dynamic frequency distribution (count of counts) with date slicer

[tmv3v]

Say I have a sample data below:

Transaction ID	Staff ID	Provider ID	Product type	Date
TR0000001	ST001	PR001	Fruits	05/01/2018
TR0000002	ST001	PR001	Fruits	06/01/2018
TR0000003	ST001	PR001	Fruits	07/01/2018
TR0000004	ST001	PR001	Fruits	08/01/2018
TR0000005	ST001	PR001	Stationary	09/01/2018
TR0000006	ST001	PR001	Stationary	10/01/2018
TR0000007	ST007	PR001	Stationary	11/01/2018
TR0000008	ST007	PR001	Stationary	12/01/2018
TR0000009	ST007	PR001	Stationary	13/01/2018
TR0000010	ST007	PR001	Stationary	14/01/2018
TR0000011	ST007	PR001	Stationary	15/01/2018
TR0000012	ST007	PR001	Stationary	16/01/2018
TR0000013	ST007	PR001	Stationary	17/01/2018
TR0000014	ST007	PR001	Stationary	18/01/2018
TR0000015	ST007	PR001	Stationary	19/01/2018
TR0000016	ST007	PR001	Stationary	20/01/2018
TR0000017	ST007	PR001	Stationary	21/01/2018
TR0000018	ST007	PR001	Stationary	22/01/2018
TR0000019	ST007	PR001	Stationary	23/01/2018
TR0000020	ST007	PR002	Stationary	24/01/2018
TR0000021	ST007	PR002	Stationary	25/01/2018
TR0000022	ST007	PR002	Stationary	26/01/2018
TR0000023	ST007	PR002	Stationary	27/01/2018
TR0000024	ST007	PR002	Stationary	28/01/2018
TR0000025	ST025	PR002	Stationary	29/01/2018
TR0000026	ST025	PR002	Drinks	30/01/2018
TR0000027	ST025	PR002	Drinks	31/01/2018
TR0000028	ST025	PR002	Drinks	01/02/2018
TR0000029	ST025	PR002	Drinks	02/02/2018
TR0000030	ST025	PR002	Drinks	03/02/2018
TR0000031	ST025	PR003	Drinks	04/02/2018
TR0000032	ST025	PR003	Drinks	05/02/2018
TR0000033	ST025	PR003	Drinks	06/02/2018
TR0000034	ST034	PR003	Drinks	07/02/2018
TR0000035	ST034	PR003	Drinks	08/02/2018
TR0000036	ST034	PR003	Drinks	09/02/2018
TR0000037	ST038	PR003	Drinks	10/02/2018
TR0000038	ST038	PR003	Drinks	11/02/2018
TR0000039	ST038	PR003	Drinks	12/02/2018
TR0000040	ST040	PR003	Drinks	13/02/2018

My question contains two main parts:

First part is that I want to create a table with a date slicer so I can filter the data I am accessing.

I want to have a result table such that if my slicer is selected as 14/01/2018 - 13/02/2018

I will have the result table:

Number of transactions	number of staff
1	1
3	2
9	1
15	1

Second part is that I want a further classification on Provider ID and Product type such that I have the result table:

Provider ID	Product type	Number of transactions	number of staff
PR001	Stationary	10	1
PR002	Stationary	1	1
PR002	Stationary	5	1
PR002	Drinks	5	1
PR003	Drinks	1	1
PR003	Drinks	3	3

I tried using extra calculated column but it is not dynamic with the slicer.

Thank you very much!

[sanalytics]

Hey @tmv3v ,

Need little clarification.
For the second part, you need classification on Provider ID and Product Type  then why same Provider ID and Product Type appears multiple times into your expected output table? for example,PR003 and releated product types drinks appear two times.

Your given data says that,No. of transaction of Provider ID "PR003" and Product Type "Drinks" is 10 but your expected output table says 4 (1,3)..

on the First Part, you need No.of Transaction and No.of Staff by which classification ?

Please Clarify the above doubts.

Regards,

snandy

[tmv3v]

Thanks for your reply! Sorry for being unclear.

All the distributions are based on staff ID. 

For the first part, the data (3, 2) means there are two distinct staff who were responsible for 3 transactions for the time period.

For the second part it's a nested frequency distribution where I want a distribution for every unique (Provide ID+Product type) combination on staff ID. So for "PR003", there is only product type - drinks and the result table is saying that there is one staff who was responsible for 1 transaction and 3 staffs who were responsible for 3 transactions, giving 1+3*3=10.

For the second part I have the idea of using matrix and putting Provider ID and Product type into the row section, and then using the drilldown button and drill all the way to the bottom. But first I have to solve the first problem.

Thank you!

[v-xuding-msft · Accepted solution]

Hi @tmv3v ,

Maybe the formulas below can help you.

• Create two calculated tables

Table 1 =
GENERATESERIES (
    1,
    MAXX (
        SUMMARIZE (
            'Table',
            'Table'[Staff ID],
            "Count of Transaction", DISTINCTCOUNT ( 'Table'[Transaction ID] )
        ),
        [Count of Transaction]
    )
)


Table 2 =
GENERATESERIES (
    1,
    MAXX (
        SUMMARIZE (
            'Table',
            'Table'[Staff ID],
            'Table'[Provider ID],
            'Table'[Product type],
            "Count of Transaction", DISTINCTCOUNT ( 'Table'[Transaction ID] )
        ),
        [Count of Transaction]
    )
)

• Create measures

Number of Staff =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[Staff ID],
            "Count", DISTINCTCOUNT ( 'Table'[Transaction ID] )
        ),
        [Count] IN FILTERS ( 'Table 1'[Number of Transaction] )
    )
)


Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[Staff ID],
            "Count", DISTINCTCOUNT ( 'Table'[Transaction ID] )
        ),
        [Count] IN FILTERS ( 'Table 2'[Number of Transaction] )
    )
)

Best Regards,

Xue Ding

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