Dynamic grouping of data

[Top006]

Hi Community,

I have a set of data similar to below:

Region	Store	Order ID	Request ID	Order date	Delivery date	Date diff (Deliv. and order)
EOS	A	00820AK1	00820AK	17-07-2020	22-07-2020	5
EOS	A	00820BP1	00820BP	20-10-2020	23-10-2020	3
EOS	A	00820CA1	00820CA	06-11-2020	12-11-2020	6
EUR	B	02220B61	02220B6	16-04-2020	22-04-2020	6
EUR	B	02220BA2	02220BA	20-04-2020	22-04-2020	2
EUR	B	02220CM2	02220CM	28-05-2020	03-06-2020	6
EUR	B	02220CM1	02220CM	28-05-2020	03-06-2020	6
EUR	C	02220DY1	02220DY	07-07-2020	13-07-2020	6
EUR	B	02220FT1	02220FT	19-08-2020	27-08-2020	8
EUR	C	02220FV1	02220FV	20-08-2020	25-08-2020	5
EUR	B	02220G61	02220G6	25-08-2020	27-08-2020	2
NWK	D	02220GY1	02220GY	07-09-2020	09-09-2020	2
EUR	B	02220I91	02220I9	02-10-2020	10-10-2020	8
EUR	C	02220IB2	02220IB	12-10-2020	08-10-2020	-4
EUR	B	02220IC1	02220IC	05-10-2020	10-10-2020	5
EUR	E	02220J01	02220J0	27-11-2020	01-12-2020	4
EUR	F	02221AK2	02221AK	17-05-2021	20-05-2021	3
EUR	F	02221B51	02221B5	24-06-2021	01-07-2021	7
EUR	F	02221CI1	02221CI	06-08-2021	12-08-2021	6
EUR	G	02221DR1	02221DR	15-09-2021	23-09-2021	8

I am trying to set up a report showing late, on time and early nominations of a given product but the definition of this should be adjustable.

My idea is to apply two separate parameters (high and low) and let these define the min and max for the “on time nominations” i.e. if low is set to 2 and high to 6 is everything below 2 defined as late, between 2 and 6 on time and above 6 early.

This then needs to be counted to visualize the count of each of the three categories for each region or store.

With the low parameter = 2 and high parameter = 6, the result would be similar to below:

Region	Early	On time	Late
EOS	0	3	0
EUR	4	11	1
NWK	0	1	0

and,

Store	Early	On time	Late
A	0	3	0
B	2	6	0
C	0	2	1
D	0	1	0
E	0	1	0
F	1	2	0
G	1	0	0

Thank you in advance for your time and assistance

[MFelix · Accepted solution]

Hi @Top006 ,

You can do the following:

• Create a table with the Nomitation time:

TypeID

Early	1
On time	2
Late	3

• Create a wath of parameter table
• Change the slicer of the what if parameter table to a between
• Add the following measure:

Nomination time = 
SWITCH (
    SELECTEDVALUE ( 'Nomination time'[Type] ),
    "Early",
        COUNTROWS (
            FILTER (
                'Nominations',
                'Nominations'[Date diff (Deliv. and order)] > MAX ( 'Low - High'[Low - High] )
            )
        ),
    "On Time",
        COUNTROWS (
            FILTER (
                'Nominations',
                'Nominations'[Date diff (Deliv. and order)] >= MIN ( 'Low - High'[Low - High] )
                    && 'Nominations'[Date diff (Deliv. and order)] <= MAX ( 'Low - High'[Low - High] )
            )
        ),
    "Late",
        COUNTROWS (
            FILTER (
                'Nominations',
                'Nominations'[Date diff (Deliv. and order)] < MIN ( 'Low - High'[Low - High] )
            )
        )
) + 0

• Create a matrix with the following configuration:
  • Rows: [Nominations]Region
  • Columns: Nomination Time[Type]
  • Values: [Nomintaiton Time]

Result below and in attach PBIX file.

[Top006]

Hi @MFelix ,

Exactly what I was trying to achieve – thank you very much for your help.

Cheers