cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
euthor Frequent Visitor
Frequent Visitor

Weighted measure (Calc. Col., DAX Measure or anything else could do the trick)

Hi all,

as I am vey new to both PBIX and DAX Measures I am adressing maybe easy questions to the community to help me step further into this powerful software!

 

This time the question is: 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 those data in my PBIX report.

 

Please Note that the database is structured this way:

- columns show the single license/brand sales amount for each week of the year (rows);

- rows are referring to the same week for both years 2017 and 2018, regrouped by year (so all 2017 data are separated from the ones of 2018, which are listed at the "bottom of the column");

 

Could someone please give me any suggestion about how to reach this goal (calculated columns, DAX measure or anything else I am currently not able/aware to quote here)  without editing the database (an Excel workbook that must not be edited)?

 

Any of your contribution is very appreciated! 

 

Regards

3 REPLIES 3
Community Support Team
Community Support Team

Re: Weighted measure (Calc. Col., DAX Measure or anything else could do the trick)

hi, @euthor

    You may try to use SAMEPERIODLASTYEAR Function and TOTALYTD Function to add two measure as below:

last year = CALCULATE(SUM(Table3[sales]),SAMEPERIODLASTYEAR(Table3[Date]))
this year = TOTALYTD(SUM(Table3[sales]),Table3[Date])

Result:

11.PNG

If it is not your case, please share sample pbix file or some data sample and expected output . You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
euthor Frequent Visitor
Frequent Visitor

Re: Weighted measure (Calc. Col., DAX Measure or anything else could do the trick)

 

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.

Super User
Super User

Re: Weighted measure (Calc. Col., DAX Measure or anything else could do the trick)

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png