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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.