How to Show My Brand vs Top 3 Competitors Brand in a Bar Chart Based on Slicer Selection -DAX

[VikcyD]

I’m working on a Power BI report where I have a sales table structured like this:

Own Brand (e.g., A, B)
Competitor Brand (e.g., Comp1, Comp2, Comp3)
Own Brand Value
Competitor Brand Value

I want to create a bar chart that dynamically shows My Brand vs Top 3 Competitor Brands when I select a brand (e.g., A or B) from a slicer.
Here’s what I’m trying to achieve:

When I select Brand A from the slicer, the chart should show:

Total value of Brand A
Values of Comp1, Comp2, and Comp3 (competitors of Brand A)

I’ve attached an example image showing the desired output:

A table with Brand A and its competitors
A bar chart titled “Me vs Other” showing Brand A total vs Comp1, Comp2, and Comp3

Question:
How can I structure my data model or DAX measures so that the chart updates dynamically based on the selected brand and shows only the relevant competitors?
Thanks in advance!

[V-yubandi-msft]

Hi @VikcyD ,
Can you let us know whether your issue has been resolved or if you are still having trouble? Your feedback is important to the community and may assist others with similar concerns.

[V-yubandi-msft]

Hi @VikcyD ,

I wanted to check if you had the opportunity to review the information provided by @anilelmastasi . Please feel free to contact us if you have any further questions. 
 

Thank you and continue using Microsoft Fabric Community Forum.

[anilelmastasi · Accepted solution]

Hello @VikcyD ,

With one table, it is not possible to do this. For that we need to create new tables.

Firstly, create a disconnected slicer table:

BrandList =
DISTINCT( 'Table'[Own Brand] )

This will help to select our brand. It should be set to single select.

Secondly, create an axis table that contains every brand name (Own + Competitor):

You can create this table in Model view -> New table

AllBrands =
UNION(
    DISTINCT( SELECTCOLUMNS( 'Table', "Brand", 'Table'[Own Brand] ) ),
    DISTINCT( SELECTCOLUMNS( 'Table', "Brand", 'Table'[Competitor Brand] ) )
)

Thirdly, create a mesaure that produces mybrands + top 3 competitors.

MeVsCompetitors =
VAR _SelectedBrand = SELECTEDVALUE( BrandList[Own Brand] )
VAR _CurrentBrand = SELECTEDVALUE( AllBrands[Brand] )

-- My Brand total
VAR _MyTotal =
    CALCULATE(
        SUM( 'Table'[Own Brand Value] ),
        FILTER(
            'Table',
            'Table'[Own Brand] = _SelectedBrand
        )
    )

-- Competitor total for current row
VAR _CompetitorTotal =
    CALCULATE(
        SUM( 'Table'[Competitor Brand Value] ),
        FILTER(
            'Table',
            'Table'[Own Brand] = _SelectedBrand &&
            'Table'[Competitor Brand] = _CurrentBrand
        )
    )

-- Rank each competitor (excluding selected brand)
VAR _Rank =
    RANKX(
        FILTER(
            ALL( 'Table'[Competitor Brand] ),
            'Table'[Competitor Brand] <> _SelectedBrand
        ),
        CALCULATE(
            SUM( 'Table'[Competitor Brand Value] ),
            FILTER(
                'Table',
                'Table'[Own Brand] = _SelectedBrand &&
                'Table'[Competitor Brand] = 'Table'[Competitor Brand]
            )
        ),
        ,
        DESC,
        Skip
    )

-- Return appropriate value per axis
RETURN
SWITCH(
    TRUE(),
    ISBLANK(_SelectedBrand), BLANK(),
    _CurrentBrand = _SelectedBrand, _MyTotal,
    _Rank <= 3, _CompetitorTotal,
    BLANK()
)

And the final step!

• In a column chart put AllBrands[Brand] in X axis, put MeVsCompetitors in Y axis.
• Create a slicer: BrandList[Own Brand] (single select)

If this solved your issue, please mark it as the accepted solution. ✅

[VikcyD]

Thank had to  make some chnages but it wrks thanks