Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

 

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

 

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

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        Anonymous , 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