Forum Discussion

Learner_SG's avatar
Learner_SG
Helper IV
4 years ago
Solved

Power BI table sum

Hi , I have a condition in which I need to calculate the usage on hourly basis. To make it easier I will explain with a table. hour usage 1 2 1 3 1 4 2 3 2 4 3 3 3 6 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Learner_SG ,

     

    1. Here are many methods to calculate the sum.

     

    • Change the summarize type ——Hour:Don't summarize ; Usage: Sum:

    • Create a measure 

     

    Measure = CALCULATE(SUM('Table'[usage]),FILTER('Table',[hour]=MAX('Table'[hour])))  
//or
//CALCULATE(SUM('Table'[usage]),ALLEXCEPT('Table','Table'[hour]))

     

    • Create a column:

     

    Column = CALCULATE(SUM('Table'[usage]),FILTER('Table',[hour]=EARLIER('Table'[hour])))  
// or 
//CALCULATE(SUM('Table'[usage]),ALLEXCEPT('Table','Table'[hour]))

     

    • Create a new table: 

     

    New Table = SUMMARIZE('Table','Table'[hour],"total usage",SUM('Table'[usage]))

     

     

       

     

    2.  if I want to narrow it down for each day and hours, how should I modify the query?

    What does "narrow down" mean? Can you please share more detail information to help us clarify your scenario?

     

     

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

amitchandak's avatar
amitchandak
Super User
4 years ago

Learner_SG ,

new column =  sumx(filter(Table,[Hour] = earlier([Hour]) ), [Usage])

 

or

a new measure

new measure =  sumx(filter(allselected(Table),Table[Hour] = max(Table[Hour]) ), Table[Usage])