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
n_n
Frequent Visitor

Market share based on slicers selection

Hi community!

 

I have a big problem with coding, hope you can help me with it!

 

I have many products, each product has diffrent launch date in multiple countries, one category, one company, one type and multiple sales dates with diffrent value

 

Some products sharing the same category, type, code, launch date.

 

Month No column is ranking the sales date based on how it is so far from the launch date.

 

I want to make a market share measure, so the final visual chart be like this:Untitled.png

 

 

 

 

Example of my data:

CodeCountryCompanyTypeProductCategoryLaunch DateSales DateValueDateDiffMonth No
B0x1USAXAABCBBB01xaVJune 2015March 2018124234Month 34
B0x1USAXAABCBBB01xaVJune 2015April 20189335Month 35
B0x1USAXAABCBBB01xaVJune 2015May 201855336Month 36
B0x1USAXAABCBBB01xaVJune 2015June 201828637Month 37
B0x1USAXAABCBBB01xaVJune 2015July 2018107038Month 38
B0x1USAXAABCBBB01xaVJune 2015August 201840939Month 39
B0x1USAXAABCBBB01xaVJune 2015September 201812940Month 40
B0x1USAXAABCBBB01xaVJune 2015October 201813741Month 41
B0x1USAXAABCBBB01xaVJune 2015November 201864042Month 42
B0x1CanadaXAABCBBB01xaVMarch 2018March 20181231Month 1
B0x1CanadaXAABCBBB01xaVMarch 2018April 20184532Month 2
B0x1CanadaXAABCBBB01xaVMarch 2018May 20182003Month 3
B0x1CanadaXAABCBBB01xaVMarch 2018June 20188124Month 4
B0x1CanadaXAABCBBB01xaVMarch 2018July 2018915Month 5
B0x1CanadaXAABCBBB01xaVMarch 2018August 201892846Month 6
B0x1CanadaXAABCBBB01xaVMarch 2018September 2018887Month 7
B0x1CanadaXAABCBBB01xaVMarch 2018October 20181028Month 8
B0x1CanadaXAABCBBB01xaVMarch 2018November 2018839Month 9
B0x1USAYBZ8193819BBB01xaVJanuary 2003January 20183123181Month 181
B0x1USAYBZ8193819BBB01xaVJanuary 2003February 20187012182Month 182
B0x1USAYBZ8193819BBB01xaVJanuary 2003March 20185779183Month 183
B0x1USAYBZ8193819BBB01xaVJanuary 2003April 20185013184Month 184
B0x1USAYBZ8193819BBB01xaVJanuary 2003May 20188762185Month 185
B0x1USAYBZ8193819BBB01xaVJanuary 2003June 20187139186Month 186

 

Thanks all!

4 REPLIES 4
AlB
Super User
Super User

Hi @n_n

Once you select a product, wouldn't code and type be already fixed?

Can you explain exactly how you'd calculate (formula I mean so that it is clear whta you need) if the selection is, for instance:

 

Product: ABC

Type: B

Country: USA

Code: B0x1

Company: Y

n_n
Frequent Visitor

Hi, @AIB !

I am really appreciate your reply!



Actually no, let me explain more;

I have many countries
One Country -> Many Corporations
One Corporate -> Different Countries
One CODE -> Many products
One Product -> Many Code
One Type -> Many products
One Product -> Many type
Product -> multiple launch dates (depend on country, or even in one Country)
One Launch date -> Many products
One category -> Many products


What I try to do:
When I select one product:
A) It will sum the value in Month1 (Launch date = Sales Date) in one country
then divide this value by total value for the all related data (same category, code, category, type) in the whole raw data but in the same Sales Date (for any launch date) in one country

B) repeat A for the next month (Launch Date +1 = Sales Date) .. and the next ...

C) this measure can work for all products, so I can put multiple products in the chart

I tried alot of measures non of them can help me so far 😞



Example for the basic idea:
Country . Code . Type . Category . Product . LD . Sales Date . Value
USA . X . A . M . Product1 . Mar2018 . Mar2018 . 500$
USA . X . A . M . Product2 . Jun2001 . Mar2018 . 600$
USA . X . A . M . Product3 . Jan2017 . Mar2018 . 700$
USA . X . A . M . Product4 . Jan2018 . Mar2018 . 800$
Canda . X . A . M . Product1 . Feb2018 . Mar2018 . 900$
USA . Y . A . M . Product1 . Jan2018 . Mar2018 . 1000$

MS% for product1 for month 1 =500/(500+600+700+800) ≈ 19%


Thanks again!

Hi @n_n

I'm still quite lost as to what needs to be done. Can you share (a sample) of your .pbix file? It would be very helpful. 

Looking at what you explain earlier (my questions in red):

 

A) It will sum the value in Month1 (Launch date = Sales Date) in one country

In WHAT country? the one specified in the slicer?

One CODE -> Many products
One Product -> Many Code
One Type -> Many products
One Product -> Many type

How can a product have many types or many codes??

 

From your numerical example I understand that once you first select the product (Product1), country and launch month and then  you want the denominator to be the sales of all products with the same category, code and type as product1 for the country and month selected (and then the rest of the months). Is that correct?

What for are the slicers Category, Type and Code that you show on your initial pic under "Denominator selection"? If this is already determined once you choose Category, country and launch date?

n_n
Frequent Visitor

@AIB
Right now I can't share the .pbix file* it's too big

Q1) yes the country specified in the slicer

Q2) It can have different type and code must have a different "launch date" , it's like a new version of it

Well, I can "deselect" some items in the Denominator slicer

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.