cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Champion
Community Champion

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
Highlighted
Community Champion
Community Champion

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
Advocate II
Advocate II

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors