cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

grouping data and visualization

Hi,

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

early.jpg

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: grouping data and visualization

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
)




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

Proud to be a Super User!




View solution in original post

1 REPLY 1
Highlighted
Super User II
Super User II

Re: grouping data and visualization

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
)




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

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors