Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How can I dynamically add a row to a table

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. 

 

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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:

     

     

     

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        4 years ago

        Hi Anonymous ,

         

        How is the formula you are using for the calculations?