I’m trying to write a measure that sums a column filtered by multiple criteria. My data has a column with different categories. I want to sum a payment column only if the individual row has two ore more of the following categories:
The problem I am having is that it seems to filter most based of the first expression but then stops. I need it to take in to account both or more. Any help would be much appreciated.
Here’s the general methodology I would use to do this:
create a one column table consisting of the categories you want to test against. Keep this disconnected from your data model.
in Power Query, unpivot your data by selecting the index/ID field that uniquely identifies each row, and then “unpivot other columns”
add a calculated column to the unpivoted table, testing whether the category for that row is in the set of categories in the disconnected table, using the IN operator. Set the column equal to 1 if yes, 0 if no
create another calculated column summing the column in 3) by index/ID and returning a 1 if sum >=2, 0 otherwise
do a SUMX of payment*column 4 above by index/ID - will return desired sum for those rows matching 2 or more categories, 0 otherwise.
if you want, filter out the 0s using the filter pane
It looks more complicated in writing than it is.
If you can provide a PBIX with sample data, I’d be glad to demonstrate if the above is insufficient for you to get to a solution.
Thanks for posting your question @matthew.wright. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
I think I am following with a good bit but if you are willing to take a look at a PBIX file I have attached one here. I really appreciate your help here. My concern if I unpivot my data that it will make my current date format unusable? Is there a way to unpivot to a new query?
Absolutely. Just reference/duplicate your original query (called say “Data”), unpivot the new table and name it “Data Unpivoted”. Don’t link the latter into your data model with a physical relationship. If you need to incorporate it into your DAX calculations, do so using TREATAS. Here’s a similar solution I posted last night that uses this exact approach:
Thanks for posting your PBIX file. I’ll get back to you later tonight with a specific solution.
Your sample data doesn’t seem to match the problem described in the first post. There is no column in the data you provided that I can see that matches the column below, against which we’ll test whether the row encompasses two or more of the criteria categories.
I think that helps clarify a bit, but it will be helpful if you could please provide a sample of the data as it currently exists (i.e. multiple options in each row). I don’t want to guess at structure, field names, etc. and provide you solution that doesn’t match your use case.
Hi @matthew.wright, we’ve noticed that no response has been received from you since the 17th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
