Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chripuck
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
parry2k
Super User
Super User

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

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.