cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors