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
- Re: 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

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

Sum of values by each category

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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

15 REPLIES 15

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

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

10-31-2018
06:55 PM

The dataset is a pretty standard transactional procurement dataset, line item invoice details.

TtlSpendbySupp is the key column with the measure.

1) This is where the formula is working as expected - the whole dataset with no filters applied.

2) date filter applied

3) a further filter applied based on a subsequent table (but with correct relationship established)

The desired outcome is that - any filter applied the TtlSpendSupp measure will also be filtered and result in a 100% allocation on the far right hand side

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

Wednesday

Hi, i really want to know, how did you get the row number 735,063.60 in total? i can't find the way to display same row number in total. If you reply this then it will solve my current problem. Reply fast please.

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

Wednesday

This was quite a while ago, but i did find the file I made it in.

```
TtlSpendbySupp =
IFERROR(CALCULATE(SUM(APAll[NET_AMOUNT_AU]),
ALLEXCEPT(APAll,APAll[SUPPLIER NAME]),
FILTER(ALLSELECTED(APAll[PTRN_POSTED]),
APAll[PTRN_POSTED]=APAll[PTRN_POSTED]),
FILTER(ALLSELECTED(ChargeCode[Charge Account]),
ChargeCode[Charge Account]=ChargeCode[Charge Account]),
FILTER(ALLSELECTED(Account[Commercial Group ]),
Account[Commercial Group ]=Account[Commercial Group ])),1)
```

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

10-31-2018
07:54 PM

I may have just stumbled across a solution to the date filter,

[quote]

TtlSpendbySupp =

CALCULATE(SUM(APAll[NET_AMOUNT_AU]),

ALLEXCEPT(APAll,APAll[PTRN_SUPP]),

FILTER(ALLSELECTED(APAll[PTRN_POSTED]),

APAll[PTRN_POSTED]<=MAX(APAll[PTRN_POSTED])))

[/quote]

And that works perfectly when i slide the date filter around. When i apply another slicer (eg. Charge Account) it fails again.

Is it a matter of listing out all the possible filters that could be applied one after another? Would the MAX function work for non-data/value based data?

EDIT - upon further testing this is not working perfectly 😞

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

10-31-2018
09:31 PM

Ok - I have messed around with this for far too many hours but through sheer guesswork I think i have this working.

TtlSpendbySupp = CALCULATE(SUM(APAll[NET_AMOUNT_AU]), ALLEXCEPT(APAll,APAll[SUPPLIER NAME]), FILTER(ALLSELECTED(APAll[PTRN_POSTED]), APAll[PTRN_POSTED]=APAll[PTRN_POSTED]), FILTER(ALLSELECTED(ChargeCode[Charge Account]), ChargeCode[Charge Account]=ChargeCode[Charge Account]))

FYI - the change to supplier name from supplier reference number (PTRN_SUPP) didn't change the functionality - just a cleansing issue with my data.

Image example of it working for the date filter

Image example of it working for a subsequent filter 'Charge Account'

So now the only issue i can see popping up is - when i remove any supplier or charge account filters (only the date filter remains in place but its irrelevant for this problem) why is there *infinity* against this suppliers figures?

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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).

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

05-15-2020
05:21 PM

In some cases, you need to filter the information or create labels if the contribution of some row in the category is bigger than x%, and you can´t use a graph to do that. Maybe for graphs is unuseful, but not in general.

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

Announcements

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Featured Topics