How can I dynamically add a row to a table

[Anonymous]

Hi,

So I will try to ask my question using a small example. I have the fllowing data model:

I have a main Sales table getting data from an excel file, the Brands and Attributes tables are getting their values from this Sales table using the following command:

Brands = DISTINCT('Sales'[Brand]).

I am trying to develop a visual like the following visual:

This visual is taking values from the Y-axis values from Attributes table and the Legend from Brands table, and the X-axis values are coming from a measure I made using DAX. Also, there is a slicer for the brands on the screen.

Now I require athird bar in this bar chart called the Rest of MArket(ROM)with brand 1 and brand 2, it should show the rest of the market percentages for each attribute, this means it should exclude the values of brand 1 and brand 2. Also, this ROM should be available in the slicer for brands.

So for example lets say we have 5 brands in the slicer Nike, Adidas, Puma, Reebok, Superdry and they have one attribute called tight fit , I select Nike and Adidas from the slicer and also the third option called ROM, the visual shows tight fit on the y axis with thee bars , one for NIke's share in this fit, one for Adidas share in the fit and the third should include all the brands not selected i.e. Puma , Reebok, Superdry. 

I have tried to explain this problem as best I could, please let me know if further explanation is required.

Thanks. 

[MFelix · Accepted solution]

Hi @Anonymous ,

For this you need to create a new table with the brands and the others line this can be achieve using the following syntax:

Brands + Others = UNION(Brands, 
                    DATATABLE ( "Brands", STRING,
                    { { "Others" } }
    ) )

Now add the following measure to you model:

Sales Selected Brands + Others = 
VAR SelectedSales =
    CALCULATE (
        SUM(Sales[Dollars]),
        INTERSECT (
            VALUES ( Brands[Brand]),
            VALUES ( 'Brands + Others'[Brand])
        )
    )
VAR UnSelectedSales =
    CALCULATE (
     SUM(Sales[Dollars]),
        EXCEPT (
            ALL ( Brands[Brand] ),
            VALUES ( Brands[Brand] )
        )
    )
VAR AllSales =
    CALCULATE (
         SUM(Sales[Dollars]),
        ALL ( Brands[Brand])
    )
RETURN
    IF (
        HASONEVALUE ( 'Brands + Others'[Brand] ),
        SWITCH (
            VALUES ( 'Brands + Others'[Brand]),
            "others", UnSelectedSales,
            SelectedSales
        ),
        AllSales
    )

See result below and in attach file:

[Anonymous]

Hi MFelix,

Thank you for the amazing answer, you just saved my life. I have a quick question, I want each of the bars in the visual to show the percentage of the total sales for the brand in that attribute. Something like the image below:

The orange bars should add up to 100% and the blue bars should add up to 100%. Could you please suggest changes in the measure to do so?

Thanks.

[MFelix]

Hi @Anonymous ,

How is the formula you are using for the calculations?

[Anonymous]

Hey MFelix,

Thanks I figured out the calculation myself.