cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mim Member
Member

Convert Scatter chart to Matrix Visual ( Show Ranking using a disconnected Dimension)

Hello

 

I have this Scatter charts,  unfortuately Scatter chart does no have conditional formating, so my best shot will be matrix visual, unfortunately, Matrix visual accept only measures in the value Area, any ideas how to approach this

 

the measure to rank was based on this solution

 

I have a working solution here using calculated columns, but if the user select two items in the slicer, it breaks, and there is no way to have a dynamic calculated column

 

so basically

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
OwenAuger Super Contributor
Super Contributor

Re: Convert Scatter chart to Matrix Visual

Hi there @mim

 

My suggestion is in this pbix, 2nd report tab.

 

  1. Create a disconnected rank table. I called this 'Rank Disconnected' with single column 'Rank Disconected'[RNK]. This column is placed on the matrix in the Rows well.
  2. Create a new measure to use in the matrix:
    Skyline Subsystem Owen =
    VAR CurrentRank =
        SELECTEDVALUE ( 'Rank Disconnected'[RNK] )
    VAR SubSystemCount =
        DISTINCTCOUNT ( Forecast[SUBSYSTEM] )
    RETURN
        IF (
            NOT ISBLANK ( CurrentRank ),
            IF (
                CurrentRank <= SubSystemCount,
                LASTNONBLANK (
                    TOPN ( CurrentRank, VALUES ( Forecast[SUBSYSTEM] ), Forecast[SUBSYSTEM], ASC ),
                    0
                )
            ),
            [Skyline subsystem Date]
        )

This measure is set up to behave similarly to your existing measure, but it uses LASTNONBLANK & TOPN to find the SUBSYSTEM with rank corresponding to the value of 'Rank Disconnected'[RNK].

 

Let me know if that helps Smiley Happy

 

Best regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Highlighted
OwenAuger Super Contributor
Super Contributor

Re: Convert Scatter chart to Matrix Visual

Hi there @mim

 

My suggestion is in this pbix, 2nd report tab.

 

  1. Create a disconnected rank table. I called this 'Rank Disconnected' with single column 'Rank Disconected'[RNK]. This column is placed on the matrix in the Rows well.
  2. Create a new measure to use in the matrix:
    Skyline Subsystem Owen =
    VAR CurrentRank =
        SELECTEDVALUE ( 'Rank Disconnected'[RNK] )
    VAR SubSystemCount =
        DISTINCTCOUNT ( Forecast[SUBSYSTEM] )
    RETURN
        IF (
            NOT ISBLANK ( CurrentRank ),
            IF (
                CurrentRank <= SubSystemCount,
                LASTNONBLANK (
                    TOPN ( CurrentRank, VALUES ( Forecast[SUBSYSTEM] ), Forecast[SUBSYSTEM], ASC ),
                    0
                )
            ),
            [Skyline subsystem Date]
        )

This measure is set up to behave similarly to your existing measure, but it uses LASTNONBLANK & TOPN to find the SUBSYSTEM with rank corresponding to the value of 'Rank Disconnected'[RNK].

 

Let me know if that helps Smiley Happy

 

Best regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

mim Member
Member

Re: Convert Scatter chart to Matrix Visual

I tried since last year all kind of workaround, I should have just asked you in the first placeSmiley Happy I knew I had to break the relationship with the rank Table, but did not know how to have a measure to relate to.

 

that's a brilliant solution

mim Member
Member

Re: Convert Scatter chart to Matrix Visual

@OwenAuger  is there a way to filter the table forecast, I am trying to have a tooltip reports to show the ITR by subsystem

 

I am using yur measure now, can you have a look here please when you have time

 

cheers

Mim

OwenAuger Super Contributor
Super Contributor

Re: Convert Scatter chart to Matrix Visual

Hi @mim

 

No problem, I suspected you might need to do something like that Smiley Happy

 

File uploaded here

https://www.dropbox.com/s/dhtcnai3btqoji2/SKYLINE%20Owen%20edit.pbix?dl=0

 

  1. Create a measure like this:
    Subsystem Matches Skyline = 
    VAR CurrentSkylineSubsystem =
        CALCULATE ( [Skyline Subsystem Owen], ALLSELECTED () )
    RETURN
        IF ( SELECTEDVALUE ( Forecast[SUBSYSTEM] ) = CurrentSkylineSubsystem, 1 )
  2. Add a Visual Level filter to the table on the Detail tab setting Subsystem Matches Skyline = 1
  3. Now the toolitp table corresonds to the SUBSYTEM you are hovering over.

 

Best regards,

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 60 members 1,124 guests
Please welcome our newest community members: