Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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? 🙂
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
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:
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
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:
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
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.
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
Proud to be a Super User!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |