Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@chripuck you can tweak attached report measure as per you need, but this will get you started.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.)
@chripuck I don't see any attachment, share thru onedrive/google drive.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k not sure if the link went through, but here it is again.
https://1drv.ms/u/s!AqlTCrBvFegrg6xgc-AVN6Rx1nKi3w?e=YgmJNf
Thanks again for your help.
I can't do attachments yet, I included a OneDrive link in my previous message.
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |