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
Anonymous
Not applicable

Creating a market share

Hi Guys,

 

I want to create a market share type of measure but i have i bit of a complicated and large data.

 

I thought of using a measure where i divide the selected data from me by another selected data.

I have a lot of categories and a timeframe for around 3 years. I want my numerator to be the product/products from Category A(maybe use it as a page level filter) that i've selected (I've already grouped the products in a calculated column) and the denominator to be the whole category but i also want to select the date from a slicer. How do i do that?

 

Thanks in advance!

 

Edit: I think my formula should logicaly look like: "Sum of the values of the selected products from Category A"/"Sum of all values from Category A" for the selected date in the slicer

1 ACCEPTED SOLUTION
themistoklis
Community Champion
Community Champion

@Anonymous

 

Create a measure like the following:

% Share = 
VAR Volume =
    SUM('Table'[Sales])
VAR AllVolume =
    CALCULATE ( SUM('Table'[Sales]),ALL('Table'[Calcuated_Column]) )
RETURN
    DIVIDE ( Volume, AllVolume )

And then create a new Table object and add your calculated column as dimension and next to it add then above measure

View solution in original post

8 REPLIES 8
themistoklis
Community Champion
Community Champion

@Anonymous

 

Create a measure like the following:

% Share = 
VAR Volume =
    SUM('Table'[Sales])
VAR AllVolume =
    CALCULATE ( SUM('Table'[Sales]),ALL('Table'[Calcuated_Column]) )
RETURN
    DIVIDE ( Volume, AllVolume )

And then create a new Table object and add your calculated column as dimension and next to it add then above measure

Anonymous
Not applicable

Thank you @themistoklis!

What if I want to add another slicer for the products?

@Anonymous

 

Can you please explain further what you are trying to do with the new slicer?

Anonymous
Not applicable

@themistoklisWell, your measure works for calculating the share of the products from the calculated column but when I select a product from that column from a slicer it seems to divide itself and the result is always 100%. What I want is to have a product slicer where, when i select some of the products from that category, it shows their share.

@Anonymous

 

Then Product will be displayed as a slicer where you can select the product you want.

 

Then on the table object you will add the category field (as dimension) which contains all categories across all products and also add similar measure which will look like this:

 

% Share = 
VAR Volume =
    SUM('Table'[Sales])
VAR AllVolume =
    CALCULATE ( SUM('Table'[Sales]),ALL('Table'[Category]) )
RETURN
    DIVIDE ( Volume, AllVolume )

 

Anonymous
Not applicable

@themistoklis

 

Maybe I'm not making myself clear. Let me try to explain.

Your measure is perfect when calculating the market share of the products that i have in the calculated column.

But when i add a slicer with the products from the calculated column and select any of them, the measure shows the share of the product/the share of the same product, which will always be 100%, when the denominator should be the whole market in that category.

 

@Anonymous

 

I dont see the same behaviour when i recreate it on PowerBI.

 

When i select something from the slicer then the table shows me the share of this product across all the products and not of its own.

 

Any chance to share your file or show with couple of images the desired output?

Anonymous
Not applicable

@themistoklis Thanks, but i've managed to work it out, by adding an "ALL Filter in the calculation of the category.

Thank you again for your time!

 

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.