YTD average on line chart

Question

Hi!&nbsp;New member here :-)&nbsp;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.&nbsp;I would like, on the same line chart, a line that shows the average water compsumption YTD, so that I can compare.&nbsp;I assume I need to make som measures, but I need a hint to get me started? Anyone? :-)&nbsp;

anonymous · Answer

Hi&nbsp;MRN8840&nbsp;,&nbsp;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

anonymous · Answer

Hi&nbsp;MRN8840&nbsp;,&nbsp;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.&nbsp;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.&nbsp;Aiolos Zhao

mrn8840 · Answer

Hi&nbsp;Anonymous&nbsp;&nbsp;Thanks for the reply!&nbsp;I forgot to mention something rather important! The building actually has three watermeters, which I sum together in the visualisation:&nbsp;&nbsp;I assume that I somehow need to add these three together also? Maybe in another measure or in the same?

mrn8840 · Answer

Hi again&nbsp;Anonymous&nbsp;!&nbsp;Thank you so much for your time :-)&nbsp;My english is not the best, so I haven't been specific enough :-( I don't need YTD, but average per day YTD...&nbsp;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:&nbsp;&nbsp;Måler_ID: ID of the meter (13 different).Aflæsning_start: Timestamp of the beginning of the readingAflæsning_slut: Timestamp of the end of the readingAflæ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')&nbsp;So all these values are mixed together in one file. And reading from 13 different meters in the same coloumn.&nbsp;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.&nbsp;Does it make sense? :-)

anonymous · Answer

Hi&nbsp;MRN8840&nbsp;,&nbsp;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 daymeasure2 = calculate(countrows(value(Aflæsning_slut)),Måler_ID in {"first_id","second_id","third_id"}) / 24finally the third measure is what you want:measure3 = measure1 / measure2Hope that's what you want, please try it and test the result, I'm afraid that I will missing something.Aiolos Zhao

Forum Discussion

YTD average on line chart

7 Replies

Related content

Average horizontal Line across the Bar chart

Line chart

Best way to add Average Line to Line chart?

Time Line Chart

Previous Year Measure for Line Chart