Forum Discussion
Complex question: calculating measures on subgroups and pairing with a slicer
I have a tough question I've spent the last few days mulling over. Sample data is as follows:
| Company | Product | 2018 Sales | 2019 Sales |
| A | X | 500 | 600 |
| A | Y | 0 | 200 |
| B | X | 0 | 400 |
| B | Z | 500 | 100 |
I'm doing a basic churn analysis and am trying to group Companies being sold to based on their comparison of 2019 to 2018 sales BUT I want to add a slicer for Product. This is important because different areas of the business might want to look at just a certain cross section of products when identifying where attrition might be happenning.
What I need to do is summarize the above table removing Product from the analysis but still being able to slice by product. Then I have a series of calculations done in a measure that categorizes each company based on the resulting data set e.g. Company A above would be considered "Growing" as it's total sales is over the growth % threshold, but if Product X is filtered out, then it would be considered a "New Customer" as it's new to the remaining Product.
I've tried and realized that you can't create a new table with SUMMARIZE as it's not hot with slicers but
5 Replies
That's a great example, thanks! I had accomplished this much, though in a much more convoluted way.
My issue now is the final complexity: I want to use the Growth Status column in your sample file as a category in a chart. I have created a seperate, unrelated table (GrowthStatus) with the 4 Growth Status categories I'm using and I use DAX Switch formula to populate the values.
I have attached an updated version of your sample PBIX illustrating the issue with two new tables at the bottom. One is the summary of the categories in this new GrowthStatus table and the other is illustrating how the equation is going wrong (the year is creating the issue.)
