Forum Discussion
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
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.
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 ......)
Products Value A 40 B 50 X 60 Y 70 AA 20 BB 30 XX 40 YY 50 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.
