cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Sum with multiple filters

Hello,

I would like to create a DAX formula that returns the Sum of the rows of a table with multiple filters,  for ex.   CALCULATE(sum(total),filter('category',product="x" || 'category',product="A").

In this case I have only used two filter conditions, But I have thousands of records where I need to filter few hundreds, so I cant use this formula to hardcore every value nor can I filter it in the page level flter, any alternalte solution to this? 

 

Regards,

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Sum with multiple filters

You should be able to use binning for this type of thing perhaps. Not really enough information to go on here. It really seems like you want to group things. You could potentially create a table of the groupings and do it that way. For example:

 

ProductGroupsTable

Product,Group

X,One

A,One

 

You relate that to your product table and you have your groups.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: Sum with multiple filters

Hi smoupre,

Thanks for your Input, for a detailed understanding pls check out my sample table where I have 7 unique records, but incase my record set increases to 1000 unique records and if I have to take a sum of few hundred rows of products it becomes difficult to hardcore every product for ex. =calculate(sum(value),filter('ProductsTable',products=A || 'ProductsTable',products=B || 'ProductsTable',products=x ......)

ProductsValue
A40
B50
X60
Y70
AA20
BB30
XX40
YY50
Highlighted
Super User IV
Super User IV

Re: Sum with multiple filters

Right, so what I am suggesting is that you put Products and Values into a table visualization or other visualization. Then, in the fields  tab of the Visualization pane you click the drop down arrow on the Products field and click "New Group". This will allow you to group Products.

 

Alternatively, you create the table I described and create all of your groups there essentially. As long as your Products table has unique values for Products you could even have products in multiple groups. Relate the two tables on Products columns. You would then add your Group column from your Groups table and your Value column from your Products table and you would have your groups.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors