Checking if a % measure is above or below its total calculation

[bbbbbunny]

Hi all!

I'm super new to PowerBI
I'm trying to create a bubblechart where bubbles will be colored depending on if the value of a measure calculated is below  or above the measure's average/calculation on the total level

The measure is
Gross margin, % =  [Absolute Margin CY]/[Net Sales CY]
Currently, the bubbles are colored based on a hardcoded value:

Margin LEGEND =

IF(

    AND(

        [Sales Change, %] > 0,

        [Gross Margin, %] >= x

    ),

    "Heroes",

    IF(

        AND(

            [Sales Change, %] > 0,

            [Gross Margin, %] < x

        ),

        "Volume Drivers",

        IF(

            AND(

                [Sales Change, %] <= 0,

                [Gross Margin, %] >= x

            ),

            "Profit Drivers",

            IF(

                AND(

                    [Sales Change, %] <= 0,

                    [Gross Margin, %] < x

                ),

                "Bleeders"

            )

        )

    )

)

Where x is the hardcoded value.
I want to change that to a dynamic value that would be similar to the number named "Average margin %" on the graph that changes based on the filters above.

Is there any way to implement that?

Thanks!

[Anonymous]

Hi lbendlin ,thanks for the quick reply, I'll add more.

Hi @bbbbbunny ,
Use the 'GENERATESERIES' function to create a field for slicers

GENERATESERIES function - DAX | Microsoft Learn

Set slicer style to 'less than or equal to'.

Margin LEGEND =
VAR _x = MIN（Field in you slicer）
RETURN
IF(
    AND(
        [Sales Change, %] > 0,
        [Gross Margin, %] >= _x
    ),
    "Heroes",
    IF(
        AND(
            [Sales Change, %] > 0,
            [Gross Margin, %] < _x
        ),
        "Volume Drivers",
        IF(
            AND(
                [Sales Change, %] <= 0,
                [Gross Margin, %] >= _x
            ),
            "Profit Drivers",
            IF(
                AND(
                    [Sales Change, %] <= 0,
                    [Gross Margin, %] < _x
                ),
                "Bleeders"
            )
        )
    )
)

The value of '_x' will dynamically change based on your slicer selection.

Best Regards,
Wenbin Zhou

[bbbbbunny]

Hi!

Thanks a lot for the answer! However, this is not exactly what I'm looking for
I believe I've worded the question incorrectly. What I'm looking for is the way to calculate measure without iterating each row. For example, my measure Gross Margin % is [Gross Margin Abs]/[Net Sales]

I have a table like this

Brand	Sub-brand	Product	Net Sales	Gross Margin Abs
Brand 1	Sub-brand 1	Product 1	1000	500
Brand 1	Sub-brand 1	Product 2	2000	1100
Brand 1	Sub-brand 2	Product 3	3500	3000
Brand 1	Sub-brand 2	Product 4	2500	700
Brand 2	Sub-brand 3	Product 5	500	300
Brand 2	Sub-brand 3	Product 6	1500	1200
Brand 2	Sub-brand 4	Product 7	750	400

And I have slicers for Brand, Sub-Brand and Product

I want the code to compare each row's (product) Gross Margin % to Gross Margin % calculated for total, filtered by the slicers. For example, if I choose brand 2, I want the code to compare Gross Margin % of each product of Brand 2 to the Gross Margin % of Brand 2 in total

[Anonymous · Accepted solution]

Hi @bbbbbunny ,

Regarding your question, please use the 'ALLSELECTED' function. Simply put, this function clears the filtering effects from the visual and retains the filtering effects of the slicer.

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Replace your 'x' with 'Measure2'

Measure = DIVIDE(SUM('Table'[Gross Margin Abs]),SUM('Table'[Net Sales]))

Measure 2 = CALCULATE(DIVIDE(SUM('Table'[Gross Margin Abs]),SUM('Table'[Net Sales])),ALLSELECTED('Table'))

Measure 3 = IF([Measure] >= [Measure 2] ,"Green","Red")

Best Regards,
Wenbin Zhou

[bbbbbunny]

Thanks a lot! This seems to have worked perfectly

I'm super new to power BI, so this is very-very helpful

[lbendlin]

yes, read about variables in DAX formulas.  Also read about SWITCH  (to make your code look a bit nicer).

[lbendlin]

yes, read about variables in DAX formulas.  Also read about SWITCH  (to make your code look a bit nicer).