cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mat87 Regular Visitor
Regular Visitor

Sum of values by each category

Greetings all,

 

I'm new to Power BI/Dax in general so apologize if this might seems obvoious but I couldn't manage to find a way to do it.

 

I basically have the outputs in column A:B, 

 

 

what I want is the output in column C (sum of values by each category).

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Sum of values by each category

Hi @Mat87,

Besides, You can add a ALLEXCEPT filter in formula when you create a calculative column.

 

=CALCULATE(SUM(Table6[Value]),ALLEXCEPT(Table6,Table6[Category]))


Then you will get expected result shown in the following screenshot.

1.PNG

Please let me know if you have any questions.

Best Regards,
Angelia

12 REPLIES 12
itayrom Member
Member

Re: Sum of values by each category

Well, you could create a calculated column with an expression such as this-

 

Total value by category = 
	VAR
		category = [Category]
	RETURN
		CALCULATE(SUM([Value]), FILTER(Table1, [Category] = category))

 

(For each row, calculate the sum of Value of all rows where Category equals to that of the current row).

 

But I'm not sure as to why you'd want to do that. Here is an incomplete list of reasons why-

1. Power BI can do such summarizations & aggregations on its own, and in real time.

For example, if you'd add a Pie Chart to your report, set Category as its legend and Value as its values, it would automatically calculate the sum of values for each category.

2. Since the total is now present in each row, aggregations/summarizations made by Power BI on that column may result in meaningless/incorrect values.

For example, if you'd add a Pie Chart to your report, set Category as its legend and 'Total value by category' as its values, you'd get the total multiplied by the number of rows with the same category(E.g. AA = 40).

3. Storing the results of such a simple calculation in a column is a waste of storage space. A measure would be a much better solution(Although both are not required in this case, since Power BI can do such summarizations on its own).

Mat87 Regular Visitor
Regular Visitor

Re: Sum of values by each category

 

Never mind I got it working.

 

By the way is there a way to do this in Powerpivot for Excel 2013? It doesn't seems to support variables. 

 

Thank you very much for you efforts.  

v-huizhn-msft Super Contributor
Super Contributor

Re: Sum of values by each category

Hi @Mat87,

Besides, You can add a ALLEXCEPT filter in formula when you create a calculative column.

 

=CALCULATE(SUM(Table6[Value]),ALLEXCEPT(Table6,Table6[Category]))


Then you will get expected result shown in the following screenshot.

1.PNG

Please let me know if you have any questions.

Best Regards,
Angelia

Mat87 Regular Visitor
Regular Visitor

Re: Sum of values by each category

Yeah that works great. 

 

Thank you veyr much, Angelia

himanshuSehgal Occasional Visitor
Occasional Visitor

Re: Sum of values by each category

I´m encountering this scenario with a little twist.. It would be amazing if you could help me with it..

 

If there is an additional column, let´s say "year" and we are using that as a slicer in our report.. how to include that filter effect in calculated column in this case. As you can see in the top part of image that 2nd last column gives the sum irrespective of year and when I use the year asdoubt.png slicer in report, that filter is ignored.

 

What change should I make in the formula to consider the filter values?

itayrom Member
Member

Re: Sum of values by each category

Calculated columns are not re-evaluated when filters are applied. For on-the-fly calculations, measures should be used.

The type of formula suggested by @v-huizhn-msft should do the trick. E.g.

Value = CALCULATE(SUM('table'[Marks]), ALLEXCEPT('table', 'table'[student], 'table'[subject]))

 

Rahul8993 Frequent Visitor
Frequent Visitor

Re: Sum of values by each category

Awesome solution  @itayrom , really helped. :-) 

StidifordN Regular Visitor
Regular Visitor

Re: Sum of values by each category

 

=CALCULATE(SUM(Table6[Value]),ALLEXCEPT(Table6,Table6[Category]))

 

This filter works perfectly up until I apply a slicer/filter to the data range the data covers.  How do I alter this formula to account for thd date changes?

 

In fact - i have any number of filters that could be applied that would affect the required total outcome of the figures.

 

 

Super User
Super User

Re: Sum of values by each category

Hi,

 

Please share a dataset and show the expected result.