Bar Chart with an average of each time range and with two columns of week day & end.

[tomerbi]

Hi everyone, 

I'm stuck with this issue:

I'm working one creating a bar chart with time (x-axis) and meter of electricity (y-axis). I have three different columns here, date, time and meter_total. Then I create a bar chart but it then shows all the value from each time. 

I would like to create a bar chart like this (with two columns) with the average data of the total_meter: 

- define the period of the time to calculate an average value of total_meter

- define the weekday and weekend and shows an average value of each column by the time period

Many thanks, appreciate it very much. 

[tomerbi]

The column is added but it seems there's something wrong with the code. It shows the red tag on the right side again like in power query editor as an error and I cannot proceed any further. 

[confidentnwrong · Accepted solution]

First, add a column to define weekdays and weekends.

Work Day = if(WEEKDAY([Date],2)>=6,"Weekend","Weekday")

(Code from this post : Solved: Weekend and Weekday Analysis - Microsoft Fabric Community )

Then, in a vertical clustered bar chart, add 01z_meter_total to the Y-axis, and choose "average" using the dropdown button. Add 01z_time to the X-axis. Add the Work Day column to the Legend.

If this post helps, please consider accepting it as a solution.

[tomerbi]

Hi,

many thanks for your reply. 

 

When addinga a new column with the code, there's an error I cannot proceed further.  

[confidentnwrong]

Apologies for the confusion.

You are creating a column through powerquery, whereas the code I've given you is DAX (calculated column).

You need to click on your table, select "Table tools" at the top then "New column".

[tomerbi]

Hi I could manage to get the weekDay now using 

= if (Date.DayOfWeek([01Z.Date],Day.Monday)+1)>=6 then "Weekend" else "Weekday"

However, I have another problem. After choosing average from the drop down of 01z_meter_total, the columns don't change. I think it's because each value of 01z_meter_total is unique to each time here below. 

 How can I set the time periods of the day to get an average of each period on the chart? 

Thank you. 

[confidentnwrong]

This is because your times are too precise.

You need to add a column where you round up or down the time.

For instance, 

RoundedTime = concatenate(
HOUR([01z_time])
, concatenate(":"
, MINUTE([01z_time))

returns a string value that you can read as the time to the minute.

You can get rid of the 2nd concatenate to have the time to the hour

[tomerbi]

The column is added but it seems there's something wrong with the code. It shows the red tag on the right side again like in power query editor as an error and I cannot proceed any further. 

 

[confidentnwrong]

Not sure why the 2 gets underlined, mine does too but I can use the column once I've pressed enter or clicked the checkmark button.