Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rrwprioste
Frequent Visitor

Scatter plot with dynamic measures

Hello!

 

Sorry for the big post but there it goes...

I'm working in a report for what I use a disconnected table to dynamic select one or more calculated measures I have. Basically, I'm using the SWITCH function to build a measure that select these other calculated measures indexed in the disconnected table . It works fine for almost all purposes, except for creating a scatter chart that plots two derived measures based on the same disconnected table.

 

Let me try to be more specific:

In summary, I want to plot the relative deviation (measure: Selected_Measure_Dev%) in the X axis,the absolute deviation (measure: Selected_Measure_Dev) in the Y axis, and have have the 'Account' field from the disconnected table in the legend.

Please see the measures DAX formulas and disconnected table sample below:

 

 

 

Selected_Measure_Dev% = 
                    VAR MySelection =
                        SELECTEDVALUE (dimAccMeasures[Measure Name (shy)];"Shy_NS (Dev %)" )
                            RETURN
                            SUMX(dimAccMeasures;
                            SWITCH (
                            TRUE ();
                            MySelection = "Shy_NS (Dev %)"; [Shy_NS (Dev %)];      
                            MySelection = "Shy_OI (Dev %)"; [Shy_OI (Dev %)];  
                            MySelection = "Shy_SM (Dev %)"; [Shy_SM (Dev %)];  
                            [Shy_NS (Dev %)])
                            )

 

 

Selected_Measure_Dev = 
                    VAR MySelection =
                        SELECTEDVALUE (dimAccMeasures[Measure Name (shy)];"Shy_NS (Dev)" )
                            RETURN
                            SUMX(dimAccMeasures;
                            SWITCH (
                            TRUE ();
                            MySelection = "Shy_NS (Dev)"; [Shy_NS (Dev)];   
                            MySelection = "Shy_OI (Dev)"; [Shy_OI (Dev)];   
                            MySelection = "Shy_SM (Dev)"; [Shy_SM (Dev)];    
                            [Shy_NS (Dev)])
                            )

  

Shy_NS (Dev) = IF (
    CONTAINS ( dimAccMeasures; dimAccMeasures[Measure Name (shy)]; "Shy_NS (Dev)" );
[Net Sales (Dev)];
    BLANK ()
)
Net Sales (Dev) = ( [Net Sales (ACT+LE)] - [Net Sales (AP)] )

 

08-04-2018 12-14-27.jpg

 

So when I create the scatter plot as described above, it shows only one data point, which is the default selection for the measures : [Shy_NS (Dev %);Shy_NS (Dev)] with the 'Net Sales' legend. This data point is fine and the numbers are correct, but the other accounts 'Order Intake' and 'Sales Margin are not showing. If I add the Index column to the details, no data points are shown whatsoever.

 

Now when performing some tests to understand what might be the issue, I tried creating some matrix table showing subtotals for different fields from the disconnected table. For some reason only the default measure aggregation is shown. Therefore I'm quite sure this is probably due to to an aggregation problem in this dynamic measure selection, but I cannot figure out a way to overcome this.

 

Please help!

2 REPLIES 2
Greg_Deckler
Super User
Super User

Would love to recreate this but need some data that I can copy and paste. In general though, I've had issues trying to use measures with Scatter Plot visual. There are just weird limitations with that Scatter Plot visual.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
rrwprioste
Frequent Visitor

Hello!

 

Sorry for the big post but there it goes...

I'm working in a report for what I use a disconnected table to dynamic select one or more calculated measures I have. Basically, I'm using the SWITCH function to build a measure that select these other calculated measures indexed in the disconnected table . It works fine for almost all purposes, except for creating a scatter chart that plots two derived measures based on the same disconnected table.

 

Let me try to be more specific:

In summary, I want to plot the relative deviation (measure: Selected_Measure_Dev%) in the X axis,the absolute deviation (measure: Selected_Measure_Dev) in the Y axis, and have have the 'Account' field from the disconnected table in the legend.

Please see the measures DAX formulas and disconnected table sample below:

 

 

 

Selected_Measure_Dev% = 
                    VAR MySelection =
                        SELECTEDVALUE (dimAccMeasures[Measure Name (shy)];"Shy_NS (Dev %)" )
                            RETURN
                            SUMX(dimAccMeasures;
                            SWITCH (
                            TRUE ();
                            MySelection = "Shy_NS (Dev %)"; [Shy_NS (Dev %)];      
                            MySelection = "Shy_OI (Dev %)"; [Shy_OI (Dev %)];  
                            MySelection = "Shy_SM (Dev %)"; [Shy_SM (Dev %)];  
                            [Shy_NS (Dev %)])
                            )

 

 

Selected_Measure_Dev = 
                    VAR MySelection =
                        SELECTEDVALUE (dimAccMeasures[Measure Name (shy)];"Shy_NS (Dev)" )
                            RETURN
                            SUMX(dimAccMeasures;
                            SWITCH (
                            TRUE ();
                            MySelection = "Shy_NS (Dev)"; [Shy_NS (Dev)];   
                            MySelection = "Shy_OI (Dev)"; [Shy_OI (Dev)];   
                            MySelection = "Shy_SM (Dev)"; [Shy_SM (Dev)];    
                            [Shy_NS (Dev)])
                            )

  

Shy_NS (Dev) = IF (
    CONTAINS ( dimAccMeasures; dimAccMeasures[Measure Name (shy)]; "Shy_NS (Dev)" );
[Net Sales (Dev)];
    BLANK ()
)
Net Sales (Dev) = ( [Net Sales (ACT+LE)] - [Net Sales (AP)] )

 

08-04-2018 12-14-27.jpg

 

So when I create the scatter plot as described above, it shows only one data point, which is the default selection for the measures : [Shy_NS (Dev %);Shy_NS (Dev)] with the 'Net Sales' legend. This data point is fine and the numbers are correct, but the other accounts 'Order Intake' and 'Sales Margin are not showing. If I add the Index column to the details, no data points are shown whatsoever.

 

Now when performing some tests to understand what might be the issue, I tried creating some matrix table showing subtotals for different fields from the disconnected table. For some reason only the default measure aggregation is shown. Therefore I'm quite sure this is probably due to to an aggregation problem in this dynamic measure selection, but I cannot figure out a way to overcome this.

 

Please help!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors