- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Sum of values by each category

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Mat87

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2017
01:30 AM

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).

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-huizhn-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2017
10:40 PM

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.

Please let me know if you have any questions.

Best Regards,

Angelia

12 REPLIES 12

itayrom

Member

Re: Sum of values by each category

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2017
03:22 AM

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

Re: Sum of values by each category

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2017
07:00 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2017
10:40 PM

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.

Please let me know if you have any questions.

Best Regards,

Angelia

Mat87

Regular Visitor

Re: Sum of values by each category

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2017
11:43 PM

Yeah that works great.

Thank you veyr much, Angelia

himanshuSehgal

Occasional Visitor

Re: Sum of values by each category

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-07-2018
09:14 AM

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 as slicer in report, that filter is ignored.

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

itayrom

Member

Re: Sum of values by each category

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-07-2018
09:47 AM

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

Re: Sum of values by each category

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2018
09:39 PM

Awesome solution @itayrom , really helped. :-)

StidifordN

Regular Visitor

Re: Sum of values by each category

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-31-2018
06:29 PM

=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.

Ashish_Mathur

Super User

Re: Sum of values by each category

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-31-2018
06:44 PM

Hi,

Please share a dataset and show the expected result.