Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cuongle
Advocate II
Advocate II

MarketShare with DAX on Power BI

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:

 

supplier.PNG

 

2. FactInvoice:

 

FactInvoice.PNG

 

I would like to display the marketshare (Sum of Spend) for one specific supplier compared to others, with slicer and pie char like this:

 

MarketShare.png

 

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?

 

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@cuongle

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! Smiley Happy

And here's the result

Selected and OTHER.gif

View solution in original post

2 REPLIES 2
Sean
Community Champion
Community Champion

@cuongle

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! Smiley Happy

And here's the result

Selected and OTHER.gif

@Sean

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.