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.

Anonymous's avatar
Anonymous
Not applicable
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.

  • Learner_SG's avatar
    Learner_SG
    Helper IV
    4 years ago

    Hi Anonymous , I was able to solve the issue .Thanks a lot.  The 2nd question also got resolved.

  • Learner_SG's avatar
    Learner_SG
    Helper IV
    4 years ago

    Anonymous , its the continuation of the question that I had asked previousl,becuase I am running into error now.

    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?

    What I mean is that I need to select the sum of usage for each hour in a DAY . now what happens is that it sums up all the usage of each hours from different days  which is not I want. Could help me on it?

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      So assuming you have a Date column as well, try this calculated column formula

      =calculate(sum(Data[usage]),filter(data,data[date]=earlier(data[date])&&data[hour]=earlier(data[hour])))

      Hope this helps.

  • Learner_SG's avatar
    Learner_SG
    Helper IV
    4 years ago

    Anonymous  Is there any other way for calculation of the usage ,because it worked fine earlier. now my data is growing bigger and the number of rows are higher and it causes power bi to have slower performance.