cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cuongle Member
Member

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

Accepted Solutions
Sean Super Contributor
Super Contributor

Re: MarketShare with DAX on Power BI

@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 Super Contributor
Super Contributor

Re: MarketShare with DAX on Power BI

@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

Highlighted
cuongle Member
Member

Re: MarketShare with DAX on Power BI

@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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors