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

market share calculation issues

Hi all,

I am struggling with DAX measurements, so I need your help even to solve easy things.

 

I want to calculate how much both "Brand" and "License" amounts weight on the total sales for Y2018, and how much this varied if compared to Y2017, so that the formula should just be like TotalBrand/TotalYear amounts (the same for License).

The goal is to have a pie chart showing off the weight of the tot. amount of License AND the weight for the Brand for both 2018 and 2017 --> be 100% the worth of the 201X market, I want to see the share of Brand (X % of total market) & the share of License (% of total market) in the pie chart.

 

Please Note that the database is structured this way:

- columns show the value of the single license/brand for each week of the year (which are also specified in rows);

- I created 2 calculated columns to have a dynamic SUM of the values for each week of the year, so these are "Total2018" and "Total2017" columns, from which I guess I should take the numerical values to calculate the Market Share for each brand and license.

- the answer MUST NOT  consider to edit the database sourcing the PBIX file!

 

Any suggestion is highly appreciated!

7 REPLIES 7
v-danhe-msft
Employee
Employee

Hi @euthor,

Could you please post some sample data to have a test and post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi all,

here's the raw file format for the data (Excel):

 

Time PeriodBrandLicenseSales
Jan 01 2018 - Jan 07 2018Galaxy wireless Mouse (Contoso)Galaxy Wires150,00 €
Jan 01 2018 - Jan 07 2018Contoso USB mouseNot Specified43,00 €
Jan 01 2017 - Jan 07 2017Galaxy wireless Mouse (Contoso)Galaxy Wires150,00 €
Jan 01 2017 - Jan 07 2017Contoso USB mouseNot Specified43,00 €
Sep 29 2018 - Sep 04 2018Galaxy wireless Mouse (Contoso)Galaxy Wires170,00 €
Sep 29 2018 - Sep 04 2018Contoso USB mouseNot Specified45,00 €
Sep 29 2017- Sep 04 2017Galaxy wireless Mouse (Contoso)Galaxy Wires170,00 €
Sep 29 2017- Sep 04 2017Contoso USB mouseNot Specified45,00 €

 

Please note that:

- the section with date is in text format (for PBIX file), and it cannot be converted it to data format as weel - already tried, PBIX cannot perform the operation;

- both  brand and license data are reported as text, while the only number format is for Sales data.

- I remind you that I cannot modify the format in any case.

 

The desired result is to display a pie chart which shows how much the 'Not Specified' total sales for weight (in terms of percentage) compared to the total amount of the ficitional Licensed goods for 2018 --> in other terms, I have to display graphically the market share of both Licensed and non-licensed goods.

 

Hope this would help you to figure out your solutions to my problem!

 

Thank you.

Hi @euthor,

Based on my test, you could refer to below steps:

Create a calculated column in your row table:

2018Sales = IF(RIGHT('Table1'[Time Period],4)="2018",'Table1'[Sales],BLANK())

1.PNG

Create a pie chart and set the value as percent of grand of total:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Daniel He,

Thank you for your contribution!!

 

Your solution is correct (and great!) if I want to show the weight for the single variable, so I can calculate them one at a time for Brand and License. However my issue is to combine them, so that one pie chart shows the total for Y2018 soplit by License and Brand.

 

I hope that I have been clear enough for you to help me out with this!

 

 

Hi @euthor,

Could you please post your desired result picture if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How can I upload here a sample file so you can better understand what my goal is?

Hi @euthor,

You could upload your report to your OneDrive or Dropbox and send the link to me.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.