Scatter Plot - Overlapping of data labels

[Vijay08V]

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!

Item	Actual Points	Actual Points (with Random number added)	Expected
A1	0	0.37	All these Item should have distinguishable value points after adding Random number and it should be within range of 0 to 1
A2	0	0
A3	0	0.13
A4	0	0
A5	0	0.36
A6	0	0
A7	0.9	1
A8	0.9	1
A9	0.9	1
A10	1	1.47
A11	1.6	1.74	Item should have distinguishable value points after adding Random number and it should be within range of 1 to 2
A12	2.4	2.61	All these Item should have distinguishable value points after adding Random number and it should be within range of 2 to 3
A13	3	3
A14	3	3
A15	3	3
A16	3	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)

[Vijay08V]

Hello @v-xingshen-msft . Thanks ! it worked.

[Vijay08V]

Hello @v-xingshen-msft 

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)

[v-xingshen-msft]

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.

[v-xingshen-msft]

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]

Hi @v-xingshen-msft 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.

[v-xingshen-msft]

Hi @Vijay08V ,

With your new requirements, we can realise your needs with only minor adjustments to our code.

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,
       0
   )
VAR MaxValue =
   SWITCH(
       TRUE(),
       BaseValue = 0, 1,
       BaseValue <1, 1,
       BaseValue <2, 2,
       BaseValue <=3, 3,
       0
   )
VAR Jitter =
   SWITCH(
       TRUE(),
       BaseValue = 0, RANDBETWEEN(1, 90) / 100,  
       BaseValue <1, RANDBETWEEN(1, 20) / 100,  
       BaseValue <2, RANDBETWEEN(1, 40) / 100,  
       BaseValue <=3, RANDBETWEEN(-35, 0) / 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]

Hi @v-xingshen-msft  - Thanks for your prompt response. I tried the above DAX measure with slight change to MinValue and MaxValue calculation to define the exact lower and upper range. But I could still see repeatation of values. I don't know where I am going wrong.

Test =

 VAR BaseValue = MAX('Table'[Actual Points])

VAR Minvalue =

   SWITCH(

       TRUE(),

       BaseValue = 0, 0,

       BaseValue <=1, 0,

       BaseValue <=2, 1,

       BaseValue <=3, 2,

       0

   )

VAR MaxValue =

   SWITCH(

       TRUE(),

       BaseValue = 0, 1,

       BaseValue <=1, 1,

       BaseValue <=2, 2,

       BaseValue <=3, 3,

       0

   )

VAR Jitter =

   SWITCH(

       TRUE(),

       BaseValue = 0, RANDBETWEEN(1, 90) / 100,  

       BaseValue <1, RANDBETWEEN(1, 20) / 100,  

       BaseValue <2, RANDBETWEEN(1, 40) / 100,  

       BaseValue <=3, RANDBETWEEN(-35, 0) / 100,  

       0

   )

VAR AdjustedValue = BaseValue + Jitter

RETURN

   MIN(MAX(AdjustedValue, Minvalue), MaxValue)

[v-xingshen-msft · Accepted solution]

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.