Forum Discussion
Sum totals from multiple columns based on filter selection
Hi,
Hope you're all doing well.
Is it possible to achieve something like this: I'd like to be able to display a total value based on multiple columns where the total will vary depending on the columns I'd like to sum by using a filter.
As an example I have this data set called 'tbl_Totals':
And I've created a measure that sums the totals like this:
Grand Total = SUM('tbl_Totals'[Pants]) + SUM('tbl_Totals'[Shirts]) + SUM('tbl_Totals'[Shoes])
Which on a table looks like this:
I'm trying to create a filtering approach where I can change the grand total based on the fields I select. So if I select 'Shoes', the Grand Total will be 80. Or, if I select 'Pants' and 'Shirts', the grand total will change to 125. Example:
'Shoes' only:
'Pants' & 'Shoes':
I just made up the slicers as a demonstration.
Thanks in advance!
4 Replies
- amitchandakSuper User
aridavid , not very cleat.
You can unpivot the data and use
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/- aridavidFrequent Visitor
hanks for the hint, I'll try to clarify.
- Greg_DecklerCommunity Champion
aridavid - Maybe, it's not very clear though. You would probably have more luck if you unpivoted those columns in your dataset. If you do need multi-column aggregation, there is a way to do it. https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-p/391698#M129
And the solution comes with a nice poem...
- aridavidFrequent Visitor
Hey, thanks for checking, I'll try to edit and clarify.