Forum Discussion

MRN8840's avatar
MRN8840
Icon for Helper I rankHelper I
7 years ago

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 prev...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • MRN8840's avatar
    MRN8840
    Icon for Helper I rankHelper I
    7 years ago

    Hi Anonymous 

     

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • MRN8840's avatar
        MRN8840
        Icon for Helper I rankHelper I
        7 years ago

        Hi again Anonymous !

         

        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? :-)