Power BI table sum

[Learner_SG]

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?

[v-eqin-msft]

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.

[v-eqin-msft · Accepted solution]

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]

@v-eqin-msft  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.

[Learner_SG]

@v-eqin-msft , 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]

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]

Hi @v-eqin-msft , I was able to solve the issue .Thanks a lot.  The 2nd question also got resolved.

[Learner_SG]

thanks @amitchandak , if I want to narrow it down for each day and hours, how should I modify the query?

[amitchandak]

@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])