Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sum of Hourly Values by Date and ID

Hello,

 

I am trying to find the measure that will sum the hourly values for each distinct ID by the date. 

 

Here is the model and desired output in last column. How can I get the Distinct Daily Total? Thanks!!

IDDateTimeValueDistinct Daily Total
A8/3/2019 0:001105
A8/3/2019 1:005105
A8/3/2019 2:003105
A8/3/2019 3:006105
A8/3/2019 4:007105
A8/3/2019 5:003105
A8/3/2019 6:002105
A8/3/2019 7:006105
A8/3/2019 8:001105
A8/3/2019 9:007105
A8/3/2019 10:008105
A8/3/2019 11:003105
A8/3/2019 12:000105
A8/3/2019 13:009105
A8/3/2019 14:004105
A8/3/2019 15:002105
A8/3/2019 16:008105
A8/3/2019 17:002105
A8/3/2019 18:008105
A8/3/2019 19:000105
A8/3/2019 20:003105
A8/3/2019 21:007105
A8/3/2019 22:002105
A8/3/2019 23:008105
B8/3/2019 0:00575
B8/3/2019 1:00275
B8/3/2019 2:00175
B8/3/2019 3:00675
B8/3/2019 4:00775
B8/3/2019 5:00475
B8/3/2019 6:00275
B8/3/2019 7:00175
B8/3/2019 8:00675
B8/3/2019 9:00375
B8/3/2019 10:00175
B8/3/2019 11:00275
B8/3/2019 12:00375
B8/3/2019 13:00175
B8/3/2019 14:00275
B8/3/2019 15:00975
B8/3/2019 16:00475
B8/3/2019 17:00575
B8/3/2019 18:00375
B8/3/2019 19:00175
B8/3/2019 20:00275
B8/3/2019 21:00275
B8/3/2019 22:00175
B8/3/2019 23:00275
1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @Anonymous ,

 

first I created a calculated column using this DAX statement to extraxt the day part from the DateTime column:

Date = DATE(YEAR('Table1'[DateTime]) , MONTH('Table1'[DateTime]) , DAY('Table1'[DateTime]))

Then, I created a measure using this DAX statement:

Measure = 
SUMX(
    VALUES(Table1[ID])
    , var maxDateTime = MAX('Table1'[DateTime])
    var maxDate = DATE(YEAR(maxDateTime) , MONTH(maxDateTime) , DAY(maxDateTime)) 
    return
    CALCULATE(
        SUM(Table1[Value])
        , ALL('Table1'[DateTime] , 'Table1'[Date] , 'Table1'[Value])
        , 'Table1'[Date] = maxDate
    )
)

This allows to create a table visual like so:

image.png

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

Hey @Anonymous ,

 

first I created a calculated column using this DAX statement to extraxt the day part from the DateTime column:

Date = DATE(YEAR('Table1'[DateTime]) , MONTH('Table1'[DateTime]) , DAY('Table1'[DateTime]))

Then, I created a measure using this DAX statement:

Measure = 
SUMX(
    VALUES(Table1[ID])
    , var maxDateTime = MAX('Table1'[DateTime])
    var maxDate = DATE(YEAR(maxDateTime) , MONTH(maxDateTime) , DAY(maxDateTime)) 
    return
    CALCULATE(
        SUM(Table1[Value])
        , ALL('Table1'[DateTime] , 'Table1'[Date] , 'Table1'[Value])
        , 'Table1'[Date] = maxDate
    )
)

This allows to create a table visual like so:

image.png

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens any idea on how to get 95th percentile by month for each data group?

 

I have tried 

95th in = PERCENTILEX.INC(Interfaces, [test], 0.95)
 
but no luck 😞 

 

Anonymous
Not applicable

Thanks. I had to delete a line of code 

 

, 'Table1'[Date] = maxDate

but it worked after I deleted. Not sure what that does exactly.. but it threw an error for me. Thanks again! 

@Anonymous another idea is to use ALLEXCEPT 

 

Daily Value = 
CALCULATE( 
SUM( 'Table'[Value] ), 
ALLEXCEPT( 'Table', 'Table'[ID] )
)

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.