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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

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

        Hi Anonymous 

         

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