March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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.
Hi @Anonymous ,
How is the formula you are using for the calculations?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey MFelix,
Thanks I figured out the calculation myself.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
90 | |
80 | |
49 |
User | Count |
---|---|
160 | |
145 | |
103 | |
72 | |
55 |