sum up measure with some conditions

[Dunner2020]

Hi there,

I have data that records the date and time of the event that happened in the past and the value of the event. I have categorized the events into half-hour blocks (using time intelligence ). The idea is the value of events that occurred in the same half-hour block should be summed up. My data looks as follow:

As you can see above three events occurred on 6/26/2019 between 9:30 am to 10:00 am in the same half-hour block i.e. 9:30 am. I created a measure [30 mins value] that sum up the events that occurred in the same half-hour block. Now I want to calculate the sum of the measure [30 mins value]. I used the following measure to calculate the sum of all event values:

All_sumup_30 = SUMX(Table1, [30 mins value]).

The problem with the above formula is that it adds up 0.00425 three times (as it appears 3 times). Is there any way of summing up where I could say if the same date has the same half-hour block more than once then consider only one times? For example, 0.00425 add with other values only once, not three times. 

Sample file can be download from here

[parry2k · Accepted solution]

@Dunner2020 try this measure and test it

Sum 30 Min = 
SUMX ( SUMMARIZE ( Table1, Table1[Date], 'Time'[Half Hour], "@30min", [30 mins value] ), [@30min] ) 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Dunner2020  I have no idea which table is what? You should always post a question with sample data and the model to understand define the problem, with partial information it is very hard to figure out the solution, and also it can save lot of back and forth.

[Dunner2020]

I have included the sample file.

[parry2k]

@Dunner2020 I guess these tables have the relation? and how they are related? one to many, many to many? if one to many which table is one side?

[Dunner2020]

Yes, there is a relation exists. The time table has one to many relations with the fact table. I also have got date table which also has one to many relations with the fact table

[parry2k]

@Dunner2020 you should add another column and extract the date from your date/time column and then add the following measure:

New Measure =
SUMX ( 
SUMMARIZE ( Table, Table[Date Only Column], Table[Half Hour Block], "@30MinValue", MAX ( Table[30 Mins Value] ),
[@30MinValue]
)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Dunner2020]

What if Half hour column comes from the other table? In my case, it is coming from the time table.