Forum Discussion

Luuky's avatar
Luuky
Helper I
4 years ago

How to create unique values for sum function?

Dear Reader,

 

Currently i am working on a dashboard with weather data. I am pretty new to PowerBI and working on improving my skills. 

The problem i am facing is:

FullSunHour needs to be seen as a unique value and once summed it needs to add to 6257,81 instead of 1794,16. 

7 seperate locations are shown but some share the same weather station (240 or 260). 

It now only sees it as 2 different values now because there are only 2 weather stations involved. 

I want it be referenced to the location name (name) which has 7 unique results in this table. 

*Due to privacy I blurred the location name out a bit.

 

Additional information:

model:

 

Formula for FullSunHour in power query editor:

= Table.AddColumn(#"Changed Type1", "FullSunHour", each ([#" Q"]*(1/360)))

 

Thanks in advance!

 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Luuky , Try a measure like

     

    Sumx(summarize(Table, Table[Name], Table[full Sun Hour]) ), [Full sun hour])

    • Luuky's avatar
      Luuky
      Helper I
      4 years ago

      Hi Amitchandak,

       

      When I implement it, this is result (named the measure SunHour):

      Does not seem to be working and the values are wrong now aswell.

       

       

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi Luuky ,

         

        The formula amitchandak  provided is correct, with just a slight error. Please try the following formula.

        fullSunHour1=
SUMX (
    SUMMARIZE (
        'KNMI DATA',
        'ALL location'[WeatherStationKey],
        'Date'[Year],
        "full", [Full sun hour]
    ),
    [full]
)

         

        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.