cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

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 @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:

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 @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:

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



Anonymous
Not applicable

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 @Anonymous ,

 

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



Anonymous
Not applicable

Hey MFelix,

Thanks I figured out the calculation myself.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors