Sum of values by each category

[Mat87]

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

[Mat87]

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 · Accepted solution]

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

[StidifordN]

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

Hi,

Please share a dataset and show the expected result.

[StidifordN]

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 

[sivasrao]

How did you visualize the table which you were shown in the answer?

I need to visualize the data in the table which is the same pattern that you were shown in the answer.

I hope I will get a quick reply from you.

[Ashish_Mathur]

Hi,

This is a very old post and long post.  Share data in a format that can be pasted in an MS Excel file, explain the question and show the expected result.

[Anonymous]

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. 

[StidifordN]

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)

[StidifordN]

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 😞

[StidifordN]

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?

[himanshuSehgal]

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?

[sivasrao]

The table was shown in the above answer from excel or power bi visuals.

If it is from power bi, how do it?

Please explain.

[itayrom]

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

[Mat87]

Yeah that works great. 

Thank you veyr much, Angelia

[itayrom]

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

[Anonymous]

---

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.

[Rahul8993]

Awesome solution  @itayrom , really helped. 🙂