How to create unique values for sum function?

[Luuky]

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!

[amitchandak]

@Luuky , Try a measure like

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

[Luuky]

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]

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.

[Luuky]

Hello @v-chenwuz-msft @amitchandak ,

Thanks for your responses, I managed to complete the dashboard using measures. Some of the measures I had to combine with Sumx(), Calculate() and DistinctCount(). 

To fix the FullSunHour problem I did the following:

Measure: FullSun = SUMX('KNMI DATA','KNMI DATA'[    Q]*(1/360))

Followed by:

Measure: NewFullSun = SUMX('All locations', [FullSun])

That somehow made it sum the correct way.

Thanks for your responses!

Greetings,

Luuk