cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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:

 

CompanyProduct2018 Sales2019 Sales
AX500600
A

Y

0200
BX0400
BZ500100

 

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 5
Highlighted
Super User IV
Super User IV

Re: Complex question: calculating measures on subgroups and pairing with a slicer

@chripuck you can tweak attached report measure as per you need, but this will get you started. 






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.





Highlighted
New Member

Re: Complex question: calculating measures on subgroups and pairing with a slicer

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.)

 

https://1drv.ms/u/s!AqlTCrBvFegrg6xgc-AVN6Rx1nKi3w?e=YgmJNf

Highlighted
Super User IV
Super User IV

Re: Complex question: calculating measures on subgroups and pairing with a slicer

@chripuck I don't see any attachment, share thru onedrive/google drive.






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.





Highlighted
New Member

Re: Complex question: calculating measures on subgroups and pairing with a slicer

I can't do attachments yet, I included a OneDrive link in my previous message.

 

Thanks!

Highlighted
New Member

Re: Complex question: calculating measures on subgroups and pairing with a slicer

@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.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors