Hi Sam,

Seen your article on dynamic grouping. Carrying over from there, how can i do a dynamic quadrant grouping for example into High- High, High-Low, Low-High and Low-Low using dax.

Thanks,

Jackson

Hi Sam,

Seen your article on dynamic grouping. Carrying over from there, how can i do a dynamic quadrant grouping for example into High- High, High-Low, Low-High and Low-Low using dax.

Thanks,

Jackson

Jackson,

Post a sample PBIX file or snapshot of what you are trying to accomplish, this will help us better provide a solution for your question. Check out this link to help you out with asking questions in the forum.

https://forum.enterprisedna.co/t/does-your-support-question-have-everything-required-to-be-answered-effectively/3953

A dynamic scatter chart like this is what i am trying to achieve.

[PS: the pic is something i did in paint for example. Not based on actual data]

This is something that can be done. It is hard to do anything without any data though. Try working on accomplishing this in a PBIX model, and when you run into an issue, post the PBIX file in here and we can better assist.

Hi,

In the file attached, I have created a grouping based on margins and created a scatter chart. But, if i need to split the figures into 4 groups as below; how do i modify my dax formula?

i. High margin High volume

ii. High margin Low volume

iii. Low margin High Volume

iv. Low margin Low volume

Regards,

Jack

Just found this comment on the link below. Currently Power BI does not support 4 quadrants.

Here is the link to the website were you can click on the link to submit your vote for Power BI to make this change.

Also check out this video here, you will see how to showcase outliers.

This is very similar to what you need, youâ€™ll likely just need to extend the secondary table and formula logic a little to get the four quadrants

Thanks

Sam

Thanks @sam.mckayâ€¦ This could work

Hi Sam. Is it possible to have a showcase example of outlier detection in combination with pareto? For example, in the tutorial video Min and Max sales are static numbers but can we put a percentage e.g. 0.8 - 1 to represent to 20% products and 0 - 0.8 to represent bottom 80% products by rank of sales? Then compare these with profit margins to classify â€śOutlierâ€ť v â€śNon-Outlierâ€ť products? In this case, the best performing quadrant will be the intersect of products which make up top 20% and have margins of 35% + etc. Keen to know how one can implement such a setup. Thanks!

This question sparked my interest, so I built out a full sample application based on External Tools Practice Dataset. By adjusting the two sliders associated with the what-if parameters, you can define dynamically what constitutes an â€śoutlierâ€ť. Those products that meet the outlier criteria will be colored red in the scatter chart. Below is a publish to web link, where you can play with the application.

Iâ€™ve also attached the solution file so you can explore how I put this together. Just give a shout if you have any questions. I hope this is helpful.

â€“ Brian

E DNA Forum â€“ ParetoMultiple Filter Solutions.pbix (428.1 KB)

1 Like

This is perfect! Great stuff and thank you Brian.

1 Like

Thanks again for a great question. Even after I completed the solution on this one, it kept my wheels spinning and I did the following YouTube video based on an extension to your original query.

Hope you find this interesting and helpful.

â€“ Brian

2 Likes

Wow! This is amazing Brian. We can now view not only the top 20% with highest margins cutoff but also view worst performing items on the same grid plus the average performers in the middle.

Absolutely - and once youâ€™ve got the general structure down, it only takes a minute or two to add those additional conditions. With just a shade more effort to distinguish between these two conditions:

you could create a fully dynamic 4-color 2x2 quadrant scatterplot, which I think would be a knockout visual.

- Brian

1 Like

Knockout visual for sure! I just think that this is a great DAX pattern that can be re-used over and over again in so many different applications. Amazing work, Brian! Thanks for this.

1 Like

So we have the 2, 1, 1, 0 matrix compared against the 1, 0, 0, 0 matrix. The former solves a lot of our problem except distinguishing between the 1â€™s. What if you just use the 1, 0, 0, 0 format and have four variables instead, as shown below. Will this work?

Outlier Color =

VAR Rnk1 = IF( [Sales Rank] <= [Sales Rank Cutoff], 1, 0 )

VAR Marg1 = IF( [Profit Margin] >= [Profit Margin Cutoff], 1, 0 )

VAR Rnk2 = IF( [Sales Rank] >= [Sales Rank Cutoff], 1, 0 )

VAR Marg2 = IF( [Profit Margin] <= [Profit Margin Cutoff], 1, 0 )

VAR Rnk3 = IF( [Sales Rank] >= [Sales Rank Cutoff], 1, 0 )

VAR Marg3 = IF( [Profit Margin] >= [Profit Margin Cutoff], 1, 0 )

VAR Rnk4 = IF( [Sales Rank] <= [Sales Rank Cutoff], 1, 0 )

VAR Marg4 = IF( [Profit Margin] <= [Profit Margin Cutoff], 1, 0 )

VAR Combined1 = Rnk1 * Marg1

VAR Combined2 = Rnk2 * Marg2

VAR Combined3 = Rnk3 * Marg3

VAR Combined4 = Rnk4 * Marg4

RETURN

SWITCH( TRUE(),

Combined1 > 0, â€ś#FF0000â€ť, // red

Combined2 > 0, â€ś#FFFF00â€ť, // yellow

Combined3 > 0, â€ś#0000FFâ€ť, // blue

Combined4 > 0, â€ś#FF6600â€ť) // orange

You could also change the TRUE and FALSE values for one dimension to 2 and 1 instead of 1 and 0. Then you add them together and you will have four possible outcomes: 0, 1,2, or 3. And if you are strategic in how you assign your initial values to each dimension, you can add 1 to the final calculation, and the result could match the corresponding quadrant number.

1 Like

@ Dave,

Like that?

1 Like

Yes, thatâ€™s even better. I had originally imagined A having possible values of 0 and 1, and then adding one to the sum of A and B, but this is even cleaner. Then, using @BrianJâ€™s measure, just assign different colors to 1, 2, 3, and 4 like you did in your post above.

Sounds great! Thanks Dave.