Forum Discussion

kevcurtis's avatar
kevcurtis
Advocate I
8 years ago
Solved

Scatter plot with non-aggregated data? Display current and desired ratings on a scale with icons

Power BI Community,

 

I'm attempting to create a visual that plots current and desired values across the X axis associated with a various topic areas as the Y axis. Each topic area contains 4 questions, with answers as a text rating which map to a numerical value (1 to 5, .5 increments) defined by a category: Snapshot (current and desired) and a Viewpoint (current and desired). Ideally I'd like to visually represent the data like a scatter plot and if one or more of the categories have the same rating the icons can be stacked on top each other.

 

With a measure I've concatenated the Snapshots/Viewpoints be grouped together in a matrix table but understand images cannot be used in this visual. I've also tried a scatter plot to no avail since it requires numerical values that are aggregated.

 

Is there a workaround for this or a way to achieve this scenario? I've browsed custom visuals and none seem to address my use case. Any help or suggestions would be greatly appreciated. Happy to send sample data if needed.

 

Thanks

Kevin

 

Below you will find a populated matrix table and the desired visual I'd like to achieve in Power BI that was mocked up in Excel.

 

Matrix table in PBI:

Desired outcome mocked up in Excel:

Need Help
Show and Tell
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi kevcurtis,

     

    How about using other icons instead? You can see it from my test.

    replacewithicon =
VAR quantity =
    SUM ( 'Sales'[Quantity] )
RETURN
    IF (
        quantity < 200,
        UNICHAR ( "9733" ),
        IF (
            quantity < 600,
            UNICHAR ( "10026" ),
            IF (
                quantity < 1500,
                UNICHAR ( "9885" ),
                IF ( quantity < 10000, UNICHAR ( "9913" ), UNICHAR ( "10057" ) )
            )
        )
    )

     

     

     

     

     

     

    You can refer to this site for other icons.

     

    Best Regards!

    Dale

     

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi kevcurtis,

     

    How about using other icons instead? You can see it from my test.

    replacewithicon =
VAR quantity =
    SUM ( 'Sales'[Quantity] )
RETURN
    IF (
        quantity < 200,
        UNICHAR ( "9733" ),
        IF (
            quantity < 600,
            UNICHAR ( "10026" ),
            IF (
                quantity < 1500,
                UNICHAR ( "9885" ),
                IF ( quantity < 10000, UNICHAR ( "9913" ), UNICHAR ( "10057" ) )
            )
        )
    )

     

     

     

     

     

     

    You can refer to this site for other icons.

     

    Best Regards!

    Dale

     

    • kevcurtis's avatar
      kevcurtis
      Advocate I
      8 years ago

      Hi v-jiascu-msft,

       

      Thank you for the suggestion! This has helped me get one step further to solving my use case. I used a measure on top of a measure to define unicode icons for each status. This works on the premise there would only be 6 statuses in total which I needed to define an icon for:

       

      Snapshot current
      Snapshot desired
      Viewpoint current
      Viewpoint desired
      Snapshot current, Snapshot desired
      Viewpoint current, Viewpoint desired

       

      Problem:

      Essentially I'm hard coding in the icons for each status value which works for the above scenario BUT in the actual data set there will be many permutations for each person in which defining an icon for each does not work (and I can't define a color for each).

       

      Ideal solution:

      I'd like to define 4 images or shapes that have transparency so they can be stacked on top of each other, dynamically, creating a shape that will define unique statuses (similar to the Excel mockup in my original post).

       

      In the short term this could work for some scenarios but with the amount of data I have I don't believe it will scale very well. Any feedback or suggestions are welcomed!

       

      Measure used:

      MStatusIcons = 
IF([MultCategory] = "Snapshot current",UNICHAR ("9679"),"")&IF([MultCategory] = "Snapshot desired",UNICHAR ("9670"),"")&
IF([MultCategory] = "Viewpoint current",UNICHAR ("9675"),"")&
IF([MultCategory] = "Viewpoint desired",UNICHAR ("9671"),"")&
IF([MultCategory] = "Snapshot current,Snapshot desired",UNICHAR ("9055"),"")&
IF([MultCategory] = "Viewpoint current,Viewpoint desired",UNICHAR ("9098"),"")

      Original measure:

      MultCategory = CONCATENATEX(VALUES(Assessment[Category_CurrentDesired]),Assessment[Category_CurrentDesired],",")

       

      Output:

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi kevcurtis,

         

        I'm a little confused. You already have the Matrix. I thought the only thing is changing the words with shapes. Maybe you can try a measure like below. Then you can define four groups or more.

        MStatusIcons =
IF (
    [MultCategory] IN { "Snapshot current", "Snapshot desired" },
    UNICHAR ( "9670" ),
    IF (
        [MultCategory] IN { "Viewpoint current", "Viewpoint desired" },
        UNICHAR ( "9671" ),
        IF (
            [MultCategory] IN { "Snapshot current", "Snapshot desired" },
            UNICHAR ( "9055" ),
            IF (
                [MultCategory] IN { "Viewpoint current", "Viewpoint desired" },
                UNICHAR ( "9098" ),
                ""
            )
        )
    )
)

        Best Regards!

        Dale