Latest Enterprise DNA Initiatives


Dynamic grouping into quadrants

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. :slight_smile:

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.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

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.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

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.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

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 :slight_smile:

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!

@Harvey,

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

@Harvey,

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.

@Harvey,

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

@Brian,

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. :wink:

1 Like

matrix

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