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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.


@ 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!:
Mastering Power BI 2nd Edition

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.