Forum Discussion

IF's avatar
IF
Post Prodigy
5 years ago
Solved

grouping data and visualization

Hi,

I have an interesting scenario for grouping . I want to group my data in the following way:

I have two different columns. One of them is for "On time", late, early. The other column gives details such as:

On time2
On time1
On time0
On time0
Late1
Early2
Late1
Early2
Early5
Early6
Late5

I want to group them: if it is on time it is 0 regardless of On time is 0, 1 or 2. Late is -, early is +. I will count values for early and late. If it is late for 1 hour, it is -1, if it is late for 4 hours more than 4 hours, it will be in the same category. Similar for the early, but it is with + value.

When I select date and name from the slicers, I want to see the data and graph in a similar way that I showed above.

Here is my example data, you can just select and copy-paste to the power bi. Also I have the file available at:https://gofile.io/d/9bSfA2

 

NameStatusHoursDate

XOn time112/2/2020
XOn time212/2/2020
XOn time012/2/2020
XOn time012/2/2020
XOn time012/2/2020
XLate112/2/2020
XEarly212/2/2020
XLate112/2/2020
XEarly212/2/2020
XEarly512/2/2020
XEarly612/2/2020
XLate512/2/2020
YOn time112/2/2020
YOn time012/2/2020
YOn time012/2/2020
YOn time012/2/2020
YLate812/2/2020
YLate512/2/2020
YEarly512/2/2020
YLate212/2/2020
YEarly412/2/2020
YEarly212/2/2020
YEarly112/2/2020
YLate312/2/2020

All the best

Need Help
  • vanessafvg's avatar
    vanessafvg
    5 years ago

    if i understand you correctly you need to create a column like this; (you will need to fill in all your scenarios i have only done a few)

    if you want them sorted in a certains sequence you can create 2 switch statements with the sequence and order by the sequence

    HoursStatus =
    SWITCH (
        TRUE (),
        'Table'[Status] = "On time", "On Time",
        'Table'[Status] = "Early"
            && 'Table'[Hours] = 1, "+1 Hour",
        'Table'[Status] = "Early"
            && 'Table'[Hours] = 2, "+2 Hour",
        'Table'[Status] = "Late"
            && 'Table'[Hours] = 1, "-1 Hour",
        'Table'[Status] = "Late"
            && 'Table'[Hours] = 2, "-2 Hour",
        "Unknown"
    )
     
     
    HoursStatusSequence =
    SWITCH (
    TRUE (),
    'Table'[Status] = "On time", 3,
    'Table'[Status] = "Early"
    && 'Table'[Hours] = 1, 4,
    'Table'[Status] = "Early"
    && 'Table'[Hours] = 2, 5,
    'Table'[Status] = "Late"
    && 'Table'[Hours] = 1, 2,
    'Table'[Status] = "Late"
    && 'Table'[Hours] = 2, 1,
    0
    )

1 Reply

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago

    if i understand you correctly you need to create a column like this; (you will need to fill in all your scenarios i have only done a few)

    if you want them sorted in a certains sequence you can create 2 switch statements with the sequence and order by the sequence

    HoursStatus =
    SWITCH (
        TRUE (),
        'Table'[Status] = "On time", "On Time",
        'Table'[Status] = "Early"
            && 'Table'[Hours] = 1, "+1 Hour",
        'Table'[Status] = "Early"
            && 'Table'[Hours] = 2, "+2 Hour",
        'Table'[Status] = "Late"
            && 'Table'[Hours] = 1, "-1 Hour",
        'Table'[Status] = "Late"
            && 'Table'[Hours] = 2, "-2 Hour",
        "Unknown"
    )
     
     
    HoursStatusSequence =
    SWITCH (
    TRUE (),
    'Table'[Status] = "On time", 3,
    'Table'[Status] = "Early"
    && 'Table'[Hours] = 1, 4,
    'Table'[Status] = "Early"
    && 'Table'[Hours] = 2, 5,
    'Table'[Status] = "Late"
    && 'Table'[Hours] = 1, 2,
    'Table'[Status] = "Late"
    && 'Table'[Hours] = 2, 1,
    0
    )