Forum Discussion

Vijay08V's avatar
Vijay08V
Helper III
2 years ago
Solved

Scatter Plot - Overlapping of data labels

Hello,

 

I am trying to add a scatter plot chart to my report based on the calculated DAX measure. I am trying to make it look like a 3x3 qudrant to show different items that fall into each quadrant. However I have challenge here. At Item field level, I have many items with same value points and when put that in Scatter plot chart ,  category and data labels overlap. Since I'm just trying to place the Items in the 3x3 quadrant , I tried adding random number to the actual Dax measure, so that each item has a distinguishable value and within the range of each quadrant block.  However I'm not able to achieve that with Rand function. I either get repeated values or the derived value with Rand numbers doesn't fall within the range.

 

Any values from 0 to 1 should be within the same range after adding rand numbers to it but however it should be distinguishable, so that its placed in the quadrant without overlapping. 

Below is the sample data and visual for understanding. Any help on this is appreciated. Thanks!

 

ItemActual PointsActual Points (with Random number added)Expected
A100.37All these Item should have distinguishable value points after adding Random number and it should be within range of 0 to 1 
A200
A300.13
A400
A500.36
A600
A70.91
A80.91
A90.91
A1011.47
A111.61.74Item should have distinguishable value points after adding Random number and it should be within range of 1 to 2
A122.42.61All these Item should have distinguishable value points after adding Random number and it should be within range of 2 to 3
A1333
A1433
A1533
A163

3

 

 

DAX Measure Used :

Actual Points (with Random number added) = VAR BaseValue = [Actual Points]
               VAR Minvalue = SWITCH(TRUE(),
                               BaseValue <1,0,
                               BaseValue <2 ,1,
                               BaseValue <3,2,0)

                               VAR MaxValue =
                                SWITCH(TRUE(), BaseValue<1,1,
                                                BaseValue < 2, 2,
                                                BaseValue < 3, 3,3)

                                                 VAR Jitter = RANDBETWEEN(0,100)/100-0.5
                                                 VAR offset_ =MOD(BaseValue*100,100)/100

                                                RETURN
                                                        MIN(MAX(BaseValue+Jitter+offset_,Minvalue),MaxValue)
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Vijay08V ,
    I made some changes on your code, we tried to use a tighter jitter control and then removed the MOD function, which allows each value to be randomly distributed and non-repeating.

     

    Actual Points (with Random number added) =
VAR BaseValue = MAX( [Actual Points])
VAR Minvalue =
   SWITCH(
       TRUE(),
       BaseValue < 1, 0,
       BaseValue < 2, 1,
       BaseValue < 3, 2,
       0
   )
VAR MaxValue =
   SWITCH(
       TRUE(),
       BaseValue < 1, 1,
       BaseValue < 2, 2,
       BaseValue < 3, 3,
       3
   )
VAR Jitter = RANDBETWEEN(1, 10) / 100 
VAR AdjustedValue = BaseValue + Jitter
RETURN
   MIN(MAX(AdjustedValue, Minvalue), MaxValue)

     

     

     

    But this time, the random value of 0 will only be changed from 0 to 0.1, not from 0 to 1.

    We can try to specify each value and then do a random number assignment.

     

    Actual Points (with Random number added) = VAR BaseValue = MAX('Table'[Actual Points])
VAR Minvalue = SWITCH(TRUE(),
BaseValue <1,0,
BaseValue <2 ,1,
BaseValue <3,2,0)
VAR MaxValue =
SWITCH(TRUE(), BaseValue<1,1,
BaseValue < 2, 2,
BaseValue < 3, 3,3)
VAR Jitter =
SWITCH(
        TRUE(),
        BaseValue = 0, RANDBETWEEN(1, 100) / 100,  
        BaseValue < 1, RANDBETWEEN(-10, 10) / 100,
        BaseValue < 2, RANDBETWEEN(1, 10) / 100,
        BaseValue < 3, RANDBETWEEN(5, 15) / 100
)
VAR AdjustedValue = BaseValue + Jitter
RETURN
MIN(MAX(AdjustedValue, Minvalue), MaxValue)

     

     

     

    If you have any other questions, you can check out the PBIX file I've shared, I hope it helps you with your question!

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Vijay08V ,
    I made some changes on your code, we tried to use a tighter jitter control and then removed the MOD function, which allows each value to be randomly distributed and non-repeating.

     

    Actual Points (with Random number added) =
VAR BaseValue = MAX( [Actual Points])
VAR Minvalue =
   SWITCH(
       TRUE(),
       BaseValue < 1, 0,
       BaseValue < 2, 1,
       BaseValue < 3, 2,
       0
   )
VAR MaxValue =
   SWITCH(
       TRUE(),
       BaseValue < 1, 1,
       BaseValue < 2, 2,
       BaseValue < 3, 3,
       3
   )
VAR Jitter = RANDBETWEEN(1, 10) / 100 
VAR AdjustedValue = BaseValue + Jitter
RETURN
   MIN(MAX(AdjustedValue, Minvalue), MaxValue)

     

     

     

    But this time, the random value of 0 will only be changed from 0 to 0.1, not from 0 to 1.

    We can try to specify each value and then do a random number assignment.

     

    Actual Points (with Random number added) = VAR BaseValue = MAX('Table'[Actual Points])
VAR Minvalue = SWITCH(TRUE(),
BaseValue <1,0,
BaseValue <2 ,1,
BaseValue <3,2,0)
VAR MaxValue =
SWITCH(TRUE(), BaseValue<1,1,
BaseValue < 2, 2,
BaseValue < 3, 3,3)
VAR Jitter =
SWITCH(
        TRUE(),
        BaseValue = 0, RANDBETWEEN(1, 100) / 100,  
        BaseValue < 1, RANDBETWEEN(-10, 10) / 100,
        BaseValue < 2, RANDBETWEEN(1, 10) / 100,
        BaseValue < 3, RANDBETWEEN(5, 15) / 100
)
VAR AdjustedValue = BaseValue + Jitter
RETURN
MIN(MAX(AdjustedValue, Minvalue), MaxValue)

     

     

     

    If you have any other questions, you can check out the PBIX file I've shared, I hope it helps you with your question!

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

  • Vijay08V's avatar
    Vijay08V
    Helper III
    2 years ago

    Hello Anonymous 

    Thank you for sharing the Pbix file. However in the snapshot if you see Item A10 has actual points (with Random) as 1.01 but it shouldn't exceed the range of 0 to 1.  Also, item A13 to A16 still get the same Actual points (with Random)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Vijay08V ，

      I'm sorry it didn't quite do what you wanted, Dax doesn't plan 1 as between 0 and 1. We can add some more filters to help randomise the distribution of the number three.

      Actual Points (with Random number added) =
 VAR BaseValue = MAX('Table'[Actual Points])
               
VAR Minvalue =
   SWITCH(
       TRUE(),
       BaseValue = 0, 0,
       BaseValue <=1, 0,
       BaseValue <2, 1,
       BaseValue <3, 2,
       BaseValue <4, 3,
       0
   )
VAR MaxValue =
   SWITCH(
       TRUE(),
       BaseValue = 0, 1,
       BaseValue <=1, 1,
       BaseValue <2, 2,
       BaseValue <3, 3,
       BaseValue <4, 4,
       4
   )
VAR Jitter =
   SWITCH(
       TRUE(),
       BaseValue = 0, RANDBETWEEN(1, 100) / 100,  
       BaseValue <=1, RANDBETWEEN(-20, 0) / 100,   
       BaseValue <2, RANDBETWEEN(1, 10) / 100,   
       BaseValue <3, RANDBETWEEN(1, 10) / 100,   
       BaseValue <4, RANDBETWEEN(1, 100) / 100,   
       0
   )
VAR AdjustedValue = BaseValue + Jitter
RETURN
   MIN(MAX(AdjustedValue, Minvalue), MaxValue)

       

       

      Hope it helps!

       

      Best regards,
      Community Support Team_ Tom Shen

      If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

       

       

      • Vijay08V's avatar
        Vijay08V
        Helper III
        2 years ago

        Hi Anonymous c- The Base value will vary between 0 to 3 and hence it can not be greater than 3 with random number added. For example Item A13 to A16 which has actual point value 3 should be within the range 2-3. 

        The base values needs to be plotted in the range of 0 to 1 , 1 to 2 and 2 to 3.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Vijay08V ，

      I'm sorry it didn't quite do what you wanted, Dax doesn't plan 1 as between 0 and 1. We can add some more filters to help randomise the distribution of the number three.

      Actual Points (with Random number added) =
 VAR BaseValue = MAX('Table'[Actual Points])
               
VAR Minvalue =
   SWITCH(
       TRUE(),
       BaseValue = 0, 0,
       BaseValue <=1, 0,
       BaseValue <2, 1,
       BaseValue <3, 2,
       BaseValue <4, 3,
       0
   )
VAR MaxValue =
   SWITCH(
       TRUE(),
       BaseValue = 0, 1,
       BaseValue <=1, 1,
       BaseValue <2, 2,
       BaseValue <3, 3,
       BaseValue <4, 4,
       4
   )
VAR Jitter =
   SWITCH(
       TRUE(),
       BaseValue = 0, RANDBETWEEN(1, 100) / 100,  
       BaseValue <=1, RANDBETWEEN(-20, 0) / 100,   
       BaseValue <2, RANDBETWEEN(1, 10) / 100,   
       BaseValue <3, RANDBETWEEN(1, 10) / 100,   
       BaseValue <4, RANDBETWEEN(1, 100) / 100,   
       0
   )
VAR AdjustedValue = BaseValue + Jitter
RETURN
   MIN(MAX(AdjustedValue, Minvalue), MaxValue)

       

       

      Hope it helps!

       

      Best regards,
      Community Support Team_ Tom Shen

      If this post helps then please consider Accept it as the solution to help the other members find it more quickly.