Using Calculation Groups with Scatter Plot to control formatting of Measures

[Anonymous]

Hi All - I was wondering if anyone had experience of using calculation groups with "measure pickers" in the context of a scatter plot;  so the user can pick which measures to show on the x axis and y-axis dynamically.  This works when using what-if parameters; but I have a mix between numerical measures and percentages, so I want to ensure the formatting follows.  I've tried created 2 measure groups for x and y in Tabular Editor as follows: 

Create a new calculation group called X-Axis

Add a calculated item for measure 1 number, Expression is selectedmeasure(), format String expression - "0.0"

Add a calculated item for measure 2 percentage, Expression is selectedmeasure(), format string expression  - "0.0%;-0.0%;0.0%"

Create a new measure - "X axis Measure Selected " as :

SWITCH ( SELECTEDVALUE ( 'X-Axis'[Ordinal] ), 
 0,[Measure 1 Calc] ,
 1, [Measure 2 Calc]
)

(Repeat the steps for Y-Axis)

Now when I put the X axis and Y axis measures in a scatter chart, with respective measure picker slicers, no data displays in the chart.  The chart is just blank.  I am guessing this is likely due to some limitations with this method becuase of the mixing of formatting in a single context; I saw a similar reply in this very helpful thread here:  https://community.powerbi.com/t5/Desktop/Dynamic-formatting-of-measures-tutorial

I don't plan to show values in the scatter necessarily, so a custom tooltip might be the way to go, but I wanted to see if anyone else has a different approach that might work?  

[OwenAuger · Accepted solution]

Hi @Anonymous 

The most common way I have seen Calculation Groups used for measure selection is to:

• Create one Calculation Group per measure to be selected.
• Within each Calculation Group, each Calculation Item corresponds to one underlying measure available for selection.
• Each Calculation Item completely replaces the selected measure with a specific measure (i.e. doesn't use SELECTEDMEASURE as part of over-ride code itself).

In order to apply this to a scatter plot, where there are two measures to be selected:

• You would need one Calculation Group per axis (as you've already described).
• You also would need a dummy measure per axis, say [X-Axis Measure] and [Y-Axis Measure]. These two measures can have any definition you like, and for simplicity I would just use BLANK ().
• The Calculation Item Expressions would then check if the appropriate dummy measure is selected using ISSELECTEDMEAUSURE, and, if so, return the desired underlying measure. Otherwise, return SELECTEDMEASURE ().
• The Format String Expressions would be set in a similar way, so that they only return the required format string if the appropriate dummy measure is selected, using ISSELECTEDMEASURE, otherwise return SELECTEDMEASUREFORMATSTRING ().
• In the scatter plot visual, [X-Axis Measure] and [Y-Axis Measure] are placed on the axes, along with appropriate field(s) in Details or Legend.
• The two Calculation Groups can be filtered using slicers to select the two measures. The axes will use the format strings defined in the Calculation Items.

I have attached a small example PBIX with 5 underlying measures that can be selected for each axis. Here is part of the script for the X-Axis Calculation Group:

------------------------------
-- Calculation Group: 'X-Axis'
------------------------------
CALCULATIONGROUP 'X-Axis'[X-Axis Measure]

    CALCULATIONITEM "Average Price" = 
        IF (
            ISSELECTEDMEASURE ( [X-Axis Measure] ),
            [Average Price],
            SELECTEDMEASURE ()
        )
        FormatString = 
            VAR FormatString =
                "\$#,0;(\$#,0);\$#,0"
            RETURN
                IF (
                    ISSELECTEDMEASURE ( [X-Axis Measure] ),
                    FormatString,
                    SELECTEDMEASUREFORMATSTRING ()
                )

    CALCULATIONITEM "Average Satisfaction" = 
        IF (
            ISSELECTEDMEASURE ( [X-Axis Measure] ),
            [Average Satisfaction],
            SELECTEDMEASURE ()
        )
        FormatString = 
            VAR FormatString =
                "0.00%;-0.00%;0.00%"
            RETURN
                IF (
                    ISSELECTEDMEASURE ( [X-Axis Measure] ),
                    FormatString,
                    SELECTEDMEASUREFORMATSTRING ()
                )

    // Plus remaining Calculation Items...

Hopefully this is close to what you were trying to be do, but please post back if needed.

Regards,

Owen

[Anonymous]

Thanks Owen, this is EXACTLY what I need, and works within my own datset. Thanks for taking the time to explain, and providing a sample! 

[amitchandak]

@Anonymous , the measure does not seem like a calculation group. In the calculation group, you have the option to give a format string

https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

[Anonymous]

The measure isn't a calculation group.  I'm formatting the individual calculation items in  the calculation group, which is then governed by the Ordinal in the switch calculation. This works in the context of say, wanting to switch the measure on a bar chart.