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.
I hope this is helpful.
- Brian