Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I got really stumped for my whole day on how to display marketshare on Power BI. My data is kind of complicated but I will try to simplify the problem in here, assume I have two tables:
1. Dimension Supplier:
2. FactInvoice:
I would like to display the marketshare (Sum of Spend) for one specific supplier compared to others, with slicer and pie char like this:
So if user choose Supplier 2, the pie chart will show the marketshare of supplier 2 compared to others. I am not sure how DAX can support this
If the Pie chart is not possible, is there any way to show this concept on the Power BI, it does not matter which visual we can use?
Solved! Go to Solution.
1) Okay so we start with your basic sum Measure
Total Spend = SUM ( 'Invoice Table'[Spend] )
2) Then we need to create another Table - NOTE this should be a disconnected table
Go to the Modeling Tab and click NEW TABLE
Supplier List Table = UNION ( VALUES ( 'Supplier Table'[Name] ), ROW ( "Supplier Name", "Other" ) )
3) Now the final Measure
Suppliers Measure = VAR SelectedSuppliers = CALCULATE ( [Total Spend], INTERSECT ( VALUES ( 'Supplier Table'[Name] ), VALUES ( 'Supplier List Table'[Name] ) ) ) VAR UnSelectedSuppliers = CALCULATE ( [Total Spend], EXCEPT ( ALL ( 'Supplier Table'[Name] ), VALUES ( 'Supplier Table'[Name] ) ) ) VAR AllSuppliers = CALCULATE ( [Total Spend], ALL ( 'Supplier Table'[Name] ) ) RETURN IF ( HASONEVALUE ( 'Supplier List Table'[Name] ), SWITCH ( VALUES ( 'Supplier List Table'[Name] ), "Other", UnSelectedSuppliers, SelectedSuppliers ), AllSuppliers )
4) Create your Slicer using the Name column from the Supplier Table
5) Create a pie chart placing Name from the Supplier List Table we created in the Legend and the Suppliers Measure in the Values
That should do it!
And here's the result
1) Okay so we start with your basic sum Measure
Total Spend = SUM ( 'Invoice Table'[Spend] )
2) Then we need to create another Table - NOTE this should be a disconnected table
Go to the Modeling Tab and click NEW TABLE
Supplier List Table = UNION ( VALUES ( 'Supplier Table'[Name] ), ROW ( "Supplier Name", "Other" ) )
3) Now the final Measure
Suppliers Measure = VAR SelectedSuppliers = CALCULATE ( [Total Spend], INTERSECT ( VALUES ( 'Supplier Table'[Name] ), VALUES ( 'Supplier List Table'[Name] ) ) ) VAR UnSelectedSuppliers = CALCULATE ( [Total Spend], EXCEPT ( ALL ( 'Supplier Table'[Name] ), VALUES ( 'Supplier Table'[Name] ) ) ) VAR AllSuppliers = CALCULATE ( [Total Spend], ALL ( 'Supplier Table'[Name] ) ) RETURN IF ( HASONEVALUE ( 'Supplier List Table'[Name] ), SWITCH ( VALUES ( 'Supplier List Table'[Name] ), "Other", UnSelectedSuppliers, SelectedSuppliers ), AllSuppliers )
4) Create your Slicer using the Name column from the Supplier Table
5) Create a pie chart placing Name from the Supplier List Table we created in the Legend and the Suppliers Measure in the Values
That should do it!
And here's the result
This is awesome answer, thanks a lot, appreciated
Now, another case is I would like to compare the marketshare of the max supplier (supplier has max spend) to others.
1. If no supplier is choosen in slicer, pie chart will show max supplier and others. In this case Supplier 1 is max supplier.
2. If only one supplier, pie chart will show this supplier and others
3. If more than one supplier are choosen, pie chart will still show max suppliers (in the list of suppliers choosen on slicer) and others.
I am not sure how I can modify your code to adapt this
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |