Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Top006
Frequent Visitor

Dynamic grouping of data

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

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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.

MFelix_0-1644431980848.pngMFelix_1-1644432011779.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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.

MFelix_0-1644431980848.pngMFelix_1-1644432011779.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Top006
Frequent Visitor

Hi @MFelix ,

 

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

 

Cheers

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.