cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mat87
Frequent 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

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

View solution in original post

13 REPLIES 13
Mat87
Frequent Visitor

 

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.  

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

View solution in original post

 

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

 

 

Hi,

 

Please share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Capture1.JPG

 

2) date filter applied

Capture2.JPG

 

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

Capture3.JPG

 

 

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 

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 😞

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

Capture4.JPG

 

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

Capture5.JPG

 

 

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?

 

Capture6.JPG

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?

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

 

Yeah that works great. 

 

Thank you veyr much, Angelia

itayrom
Resolver II
Resolver II

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


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.

Awesome solution  @itayrom , really helped. 🙂 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.