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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sarunas
Frequent Visitor

Dynamically grouping data to quadrants based on comparison vs average

Hi,

 

I got a little bit stuck with one piece of analysis, hopefully I can get some help from you.

 

I want to be able to dynamically split my data to 4 quadrants. I am able to do that visually in scatter plot, as in the screenshot below:

 

X axis: Metric A, Y axis: Metric B.

Logic: If Metric A < Metric A subset average && Metric B > Metric B subset average, then the quadrant is "Grow". And so on, following the same logic.

 

Visually, it is easy to do by adding average lines to my scatter plot.

Sarunas_0-1650875147350.png

 

Now, the tricky part. I want to transfer this grouping to a table, in order to see how many data points are in each qudrant.

Here is an example in the screenshot below (I used Excel, numbers are random).

Sarunas_2-1650876236759.png

 

So, essentially, I need to have DAX compare each data point's Metric A and Metric B to the selected subset's average and get me back the name of one of the four quadrants. I need to be able to use it as a row in a table.

 

 

Create Custom Dynamic Groups In Power BI - Advanced DAX 

This is the best resource I could find in the forums/youtube so far, but it does not help 100%, since it does not cover the dynamic part of it.

In my case, grouping should be dynamic (based on the average of 2 metrics for selected data at the moment).

 

I appreciate your help.

 

Best regards,

Sarunas

5 REPLIES 5
amitchandak
Super User
Super User

@Sarunas , refer if this blog can help

 

Customer Retention Part 4:Customers to Retain- Segment in 4 quadrant based on Margin % and Discount: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-4-Customers-to-Retain-Segmen...

Hi @amitchandak,

 

This looks like exactly what I need, I tried it out, but unfortunately it does not work for me yet.

 

- I see you created "Color dot" as a measure. How did you make it a legend in a scatter plot?

- When you add "Discount %"and "Margin %" as variables in your measure, how do you make the "Color dot" measure dynamic (i.e. react to slicers/filters)?

 

I appreciate your reply.


BR,

Sarunas

@Sarunas , Created this and used in conditional formatting using the filed value option

 

Color Dot =
var _avgDisc = CALCULATE([Discount %],ALL(Sales)) // you can use allselected, overall avg
var _avgMargin = CALCULATE([Margin %],ALL(Sales))
return switch(TRUE(),
[Margin %]>=_avgMargin && [Discount %] <=_avgDisc , "Green",
[Margin %]>=_avgMargin && [Discount %] >_avgDisc , "Blue",
[Margin %]<_avgMargin && [Discount %] <=_avgDisc , "Yellow",
[Margin %]<_avgMargin && [Discount %] >_avgDisc , "Red", "Black")

 

 

But for that to work, you can not is legend

@amitchandak, I see. Unfortunately, that does not help. What I need here, is to be able to have these quadrants (Grow, Protect, Harvest, Question) as rows in a separate table, so I could make new measures, calculating what is the value of each quadrant, how many products in each one, etc. 

 

But your suggestion does not work, the same constaint applies as with adding it as a legend.

 

I need something similar to the solution in this video. The issue there is, the groups are made static (e.g. ranks 1-20, 2-50). I need them to be dynamic - data point should compare to the average, which is not static.

 

Do you have any other ideas by any chance?

 

BR,

Sarunas

HI @Sarunas,

In my opinion, I think you can't fully achieve your requirement. These segments are defined manually(they can also be generated by an interval) but they can't interact with a slicer or other filter operations.(calculate column/tables are the parents of the virtual table which slicer measure expression used)

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors