Forum Discussion
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 |
| 4 | 6 |
| 4 | 1 |
I need to come up with a condition where I need to calculate the hourly basis usage. So for hour 1 - the sum value should be 9 ,2 should be 7 and so on. So for this purpose,how should I proceed? This data has to be used for a calculation later. So I should proceed to create a caclulated column/table?
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.
8 Replies
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.Hi Anonymous , I was able to solve the issue .Thanks a lot. The 2nd question also got resolved.
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?
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.
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.
Hi Learner_SG ,
Hope this can help:
Measure = CALCULATE(SUM('Table'[usage]),FILTER('Table',[Date]=MAX('Table'[Date])&& [hour]=MAX('Table'[hour]))) //or //CALCULATE(SUM('Table'[usage]),ALLEXCEPT('Table','Table'[Date],'Table'[hour]))Output:
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.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])
thanks amitchandak , if I want to narrow it down for each day and hours, how should I modify the query?
