cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shou
Helper I
Helper I

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:

temp3.png

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:

 

temp.PNG

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. 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @shou ,

 

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:

MFelix_0-1657011342775.png

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

Hi @shou ,

 

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:

MFelix_0-1657011342775.png

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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:
temp.PNG

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.

Hi @shou ,

 

How is the formula you are using for the calculations?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hey MFelix,

Thanks I figured out the calculation myself.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.