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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MRN8840
Helper I
Helper I

YTD average on line chart

Hi!

 

New member here 🙂

 

I have a line chart showing to water usage (in m3) in a building for the last 8 days. The data comes from a Power BI Dataflow every morning with readings from the previous day. One reading every hour; 24 readings per day.

 

I would like, on the same line chart, a line that shows the average water compsumption YTD, so that I can compare.

 

I assume I need to make som measures, but I need a hint to get me started? Anyone? 🙂

 

Vand.png

7 REPLIES 7
AiolosZhao
Memorable Member
Memorable Member

Hi @MRN8840 ,

 

As you said, yes, you need a measure to calcualte the avg value of YTD, then put it as value, then I think that's what you want.

I assume that you only have 1 year data, then the YTD measure can be:

YTD = sum(your_water_usage) / countrows(value(your_date_column))

please try.

Aiolos Zhao





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

Proud to be a Super User!




Hi @AiolosZhao 

 

Thanks for the reply!

 

I forgot to mention something rather important! The building actually has three watermeters, which I sum together in the visualisation:

 

Filters.png

 

I assume that I somehow need to add these three together also? Maybe in another measure or in the same?

Hi @MRN8840 ,

 

I think that's depands on what you want, if you want to sum 3 together, then you can do that in a measure I think.

 

But according to your screenshot, it seems that you use filter to get your 3 watermeters type,

if your watermeter data is in one column, but you need 3 of watermeters type, then you can use filter to do it.

if what I understand is wrong, then you can try to sum the 3 watermeters column together in one measure.

You can try it.

 

Aiolos Zhao





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

Proud to be a Super User!




Hi again @AiolosZhao !

 

Thank you so much for your time 🙂

 

My english is not the best, so I haven't been specific enough 😞 I don't need YTD, but average per day YTD...

 

Maybe I should describe my dataflow thoroughly. Every night at 3 am my dataflow is updated with readings for the previous day. It is one file with a mix of readings from 13 different meters. 24 readings from each meter = 312 lines added per day. It looks like this:

 

Aflæsning.png

 

Måler_ID: ID of the meter (13 different).

Aflæsning_start: Timestamp of the beginning of the reading

Aflæsning_slut: Timestamp of the end of the reading

Aflæsning: Reading (usage from 'Aflæsning_start' to 'Aflæsning_slut')

Enhed: Unit (MWh = heat, kWh = electricity, m3 = water)

Aflæsnings_døgn: Day of readed (extracted from 'Aflæsning_start')

 

So all these values are mixed together in one file. And reading from 13 different meters in the same coloumn.

 

In order to get a daily average for the water usage, I first need to sum together the readings from three meters ('Vand_161654', 'Vand_161655' and 'Vand_161656') per day and then divide it by the number of days YTD.

 

Does it make sense? 🙂

Hi @MRN8840 ,

 

Sorry for reply late, it is very clearly, thanks for the sample.

Now I think firstly you can create a measure to sum the meters that your want:

measure1 = calculate(sum(Aflæsning),Måler_ID in {"first_id","second_id","third_id"})

then I think you need to count the number of day

measure2 = calculate(countrows(value(Aflæsning_slut)),Måler_ID in {"first_id","second_id","third_id"}) / 24

finally the third measure is what you want:

measure3 = measure1 / measure2

Hope that's what you want, please try it and test the result, I'm afraid that I will missing something.

Aiolos Zhao





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

Proud to be a Super User!




Hi @AiolosZhao 

 

Sorry to have kept you waiting, I have been on holiday for the last three weeks 🙂

 

Your first measure works, but the problem is it only sums up the number of days in the visualization. I need it to sum up all the numbers from january 1st.

 

Skærmbillede 2019-08-13 kl. 10.44.30.png

 

As you can see the result of the first measure is only 109.22 = the measures for the last eight days. I need the sum from the whole year.

 

In my project I also have a date table including i coloumn with 'Day in year'. Does this make it easier? 🙂

 

Thank you so much 🙂

Hi @MRN8840 ,

 

measure1 = calculate(sum(Aflæsning),Måler_ID in {"first_id","second_id","third_id"})

I'm not sure why above measure only show 8 days in your chart, because you  can see in the measure, there is no any limitation about the date, and if your chart is also no limitation about the date, I think maybe for the 3 id that you choose, there is only 8 days data for them, right?

if not, could you please show your dimension and expression of you chart?

Thanks.

Aiolos Zhao





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

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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