How to build a dashboard showing the data which overs a limit?

[Anonymous]

Hi everyone,

I want to build a dashboard, which will only shows the data when the working hour over limit weekly.

like the following:

There are buttons to click to see the related data. (It's just a draft, I'm welcome for any other suggestion)

Draft

And I got the following two Excel files for data source.

(1)

(2)

Is it possible to build a dashboard like the draft with the two Excel file I got? 

Or any methods other than using Power BI?

Thanks a lot!

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I create a sample test table from your data. Here is my test table.

You can do some steps as follows.

1. create a column to calculate the working hours.

2. create a calendar table to  prepare for filters.

3. create a measure to summarize the weekly working hours. You can test it in a matrix visual.

4.create a slicer using "calendar[Date]" and go to settings----"general"----"orientation"-----choose "Horizontal"

5. create a measure(">=20") and a table visual, then drag it to the filter of this visual and select "=1"

Measure = 

var x1=SELECTEDVALUE('calendar'[Date])

return

IF([Sum of weekly working hour]>=20&&MAX('Test'[Date])>=x1-4&&MAX('Test'[Date])<=x1,1,0)

Result:

Of course, you can create a new measure which is similar to the above one to filter "working hours>=30".

Hope that's what you were looking for.

Best Regards,

Yuna

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

[v-yuaj-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.😊

Best Regards,

Yuna

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I create a sample test table from your data. Here is my test table.

You can do some steps as follows.

1. create a column to calculate the working hours.

2. create a calendar table to  prepare for filters.

3. create a measure to summarize the weekly working hours. You can test it in a matrix visual.

4.create a slicer using "calendar[Date]" and go to settings----"general"----"orientation"-----choose "Horizontal"

5. create a measure(">=20") and a table visual, then drag it to the filter of this visual and select "=1"

Measure = 

var x1=SELECTEDVALUE('calendar'[Date])

return

IF([Sum of weekly working hour]>=20&&MAX('Test'[Date])>=x1-4&&MAX('Test'[Date])<=x1,1,0)

Result:

Of course, you can create a new measure which is similar to the above one to filter "working hours>=30".

Hope that's what you were looking for.

Best Regards,

Yuna

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

[Anonymous]

Thank you so much for your detailed answer! It's also very useful and will make reference to it. Thank you!!

[AllisonKennedy]

@Anonymous  It looks like you have already done some calculations in Excel. These two files look like the same info in two formats. Which is the raw data? It will be best to use that raw format as the source for Power BI, and do all the calculations and transformations in Power BI. 

Also, how does this update from week to week? Ideally you want to have those selections for date range dynamically update so you don't need to rebuild the report each week. 

Please share a sample file (via OneDrive or other service) of the orginal data either as export direct from your database or in the format it is entered into Excel. 

From there we can help you better with your required dashboard/report setup.

Please @ mention me so I don't lose this thread. 

[Anonymous]

@AllisonKennedy 

Thank you so much for your detailed reply!

Here is the raw file:

https://1drv.ms/x/s!AtRpvjeBYBZ0lX3ZL5eQh_54pRh_

For update data, I am thinking to use an excel file to store all the data and it will be linked to Power BI, afterward any new data would be copied into this file, as only around 30-40 fileds of new data will be generated each time.

[AllisonKennedy · Accepted solution]

@Anonymous  See what you think of the attached pbix file (below signature). It gives option to select limit (20, 30 hrs) and Week End date to filter the table.

For updating the data, you can use Get Data > From Folder and just keep appending the latest Excel file into the pbix.

[Anonymous]

This is what I want! Thank you so much and much!!