98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » DAX formula
DAX formula
Resolved · Urgent Priority · Version 2013
Wed 15 May 2019, 18:06Delegate Tsudoi said...
Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course
DAX formula
Hi,
I have a simple sales table in data model with 3 different product hierarchies from lowest to highest as follows.
Brand->Department->Market
Eg: Nestle->Food->France
And I’d like to show in pivot table Brand share % of Department and Department share % of Market in a separately column for each.
What’s the best/easiest DAX formula to do this please?
Thanks,
Wed 15 May 2019, 21:34Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
Thank you for the forum question.
To be able to answer the question I need some more information. If the PivotTable has to show all three columns at the same you will end up with something like
Brand Dept Market Sales %Brand %Dept %Market
Nestle Food France 1000 66% 100% 50%
Nestle Food UK 1000 66% 100% 33%
MFood Food France 500 33% 100% 50%
MFood Food Spain 500 33% 100% 17%
If this is what you want, you can use the DAX below.
=CALCULATE(sum([Sales]),filter(Table1,[Brand]=EARLIER([Brand])))/sum([Sales])
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 16 May 2019, 12:36Delegate Tsudoi said...
RE: DAX formula
Sorry, I meant to say, I want to Brand %/Dept and Brand %/Market.
So, something like this. XX% is what I want pivot table to show in a separate column.
Brand Dept Market Sales Brand %/Dept Brand %/Market
Nestle Food France £10,000 XX% XX%
Nestle Drink France £55,000 XX% XX%
Nestle Food UK £12,000 XX% XX%
Pepsi Drink Italy £7,000 XX% XX%
Pepsi Food Germany £80,000 XX% XX%
Brand Total Dept Total Market Total £164,000 100% 100%
I hope that's clearer.
Regards
Tsudoi
Thu 16 May 2019, 14:31Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
To be able to do this it must be a column calculation so I will need to know which row field you want in the PivotTable.
Do you want to have the PivotTable grouped by Brand?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 16 May 2019, 17:17Delegate Tsudoi said...
RE: DAX formula
Hi Jens,
Thanks for your reply.
Not so sure what you mean by grouped by Brand. I was not thinking of any grouping in my pivot table.
I simply want to bring in Brand, Dept and Market into ROWS and Sales data into VALUE..
thanks,
Thu 16 May 2019, 22:00Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
Please have a look at attached workbook.
If it is not what you are looking for please provide me the math you want me to transfer to DAX.
Thanks
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
tsudoi.xlsxFri 17 May 2019, 11:26Delegate Tsudoi said...
RE: DAX formula
Hi Jens,
Thanks very much for your reply.
I initially simplified the source data and sent it to you to make it easier but to due to the way in which the EALIER function works, I think we need to consider all other dimensions, correct?
I say this because when I tried to use the function in the same way you did, it returns answers more than 100% which cannot be.
In terms of transaction data, I have more than just sales but I have other factors as well such as Customer and Time but I believe this isn’t the issue.
In terms of dimensions, the source data has more than just Brand/Dept/Market as follows (eg Year, Month, Division, Promo Code) and that’s where the issue is, I think.
In this case, I assume it’s only a matter of ampersandinf more dimensions in the EALIER formula but that’s what I am not sure how/why.
If that is the case here, what would be correct formula to pull this off correctly?
Year Month No Market Group Division Department Brand Promo Code Sales Customer Time
2017 1 UK Home Style Toys,Crafts & Leisure Collectables Charlie Bears IP £3,527,067 2,994 20
2017 2 Frace Beauty Beauty Cosmetics Doll10 OTHER £4,861,459 2,036 40
2018 1 UK Home Innovation Home Improvements Garden Hardgoods Plow & Hearth IP £701,669 211 20
2017 4 Italy Home Innovation Home Improvements Household Electrical Bissell TSB £1,765,356 640 42
2017 5 Germany Beauty Beauty Skincare Alpha-H FP £6,047,543 3,891 22
2017 2 Denmark Beauty Beauty Skincare Flora Mare IP £1,877,540 847 11
2017 1 UK Home Innovation Electronics Computers Canon IP £434,482 115 50
2018 2 Frace Home Innovation Electronics Electronic Apple IP £9,111,710 2,763 24
2017 4 UK Jewellery & Accessories Accessories & Shoes Shoes Emu Australia OTB £7,195,532 1,571 11
2017 5 Italy Beauty Beauty Skincare Decleor IP £18,792,974 6,939 34
2018 5 Germany Jewellery & Accessories Accessories & Shoes Shoes Clarks Footwear IP £9,403,857 2,520 44
2018 3 Denmark Apparel Apparel Classics C. Wonder FP £941,908 79 22
2017 3 UK Jewellery & Accessories Accessories & Shoes Handbags & Accessories Dennis Basso FP £3,319,153 323 39
2017 1 Frace Jewellery & Accessories Accessories & Shoes Shoes Emu Australia SALE £7,195,532 1,571 49
2018 1 UK Apparel Apparel Casuals Together OP £7,697,149 667 31
2017 2 Italy Jewellery & Accessories Jewellery Silver Bronzo Italia OTHER £2,646,748 473 34
2017 4 Germany Jewellery & Accessories Accessories & Shoes Shoes Skechers SALE £25,269,484 15,655 19
2017 1 Denmark Jewellery & Accessories Accessories & Shoes Shoes Skechers OTHER £25,269,484 15,655 59
2018 5 Denmark Home Innovation Electronics Electronic Emma Bridgewater TSB £237,753 189 33
2017 2 UK Apparel Apparel Intimate Apparel Vercella Vita SALE £4,249,404 686 23
2017 1 UK Apparel Apparel Classics Silvia Mori SALE £9,983 1 40
2018 4 Frace Jewellery & Accessories Accessories & Shoes Handbags & Accessories Frank Usher SALE £6,701,290 1,729 19
2018 2 Italy Apparel Apparel Classics Joe Browns SALE £5,414,541 1,702 38
thanks,
Fri 17 May 2019, 13:52Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
I am sorry but it doesn't help to send more data.
I will need to know the math you want to convert to DAX.
I have transformed the DAX to normal Excel functions (see attachment)
I hope this will help you to see the logic in my DAX and guide me to the math you expect.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Mon 20 May 2019, 09:26Delegate Tsudoi said...
RE: DAX formula
Hi Jens, thanks for your reply.
So I think the calculation would be something like this.
Frist to get Brand Total
=SUMIFS([Sales],[Year],[@Year],[Month No],[@[Month No]],[Market],[@Market],[Department],[@Department],[Brand],[@Brand])
Second to get Department Total
=SUMIFS([Sales],[Year],[@Year],[Month No],[@[Month No]],[Market],[@Market],[Department],[@Department])
Third to get Brand%/Dept
=[@[Brand Total]]/[@[Dept Total]]
Last to get Brand%/Market
=[@[Brand Total]]/[@[Market Total]]
I hope that makes sense?
Thanks,
Mon 20 May 2019, 10:35Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
I have done one of the them. Please see below.
I cannot check it, but please let me know if it is not working.
It must be a column calculation and you will need to change the table name in the filter function to your table name.
=calculate(sum([Sales]),filter(table1,[Year]=earlier([Year])&&[Month No]=EARLIER([Month No])&&[Market]=Earlier([Market])&&[Department]=Earlier(Department])))
In the measure area create create a measure:
Brand%/Market:=[Department Total]/[Market Total]
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 20 May 2019, 16:54Delegate Tsudoi said...
RE: DAX formula
Hi Jens,
Thanks for your reply.
I’ve tried with the formula with actual table name (Brand Data) and actual sales column name (Order Prd Amt), as follows.
Brand Sales Total:=CALCULATE(sum([Order Prd Amt]),FILTER('Brand Data',[Year]=EARLIER([Year])&&[Month No]=EARLIER([Month No])&&[Market]=EARLIER([Market])&&[Department]=EARLIER([Department])))
But, the syntax is said to be incorrect and I don’t know why.
Please, what’s doing wrong here…?
Thanks,
Mon 20 May 2019, 18:41Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
I cannot see you have a syntax error.
Did you do this as a column calculation? This calculation cannot be done down under the table in the measure area, it must be done in new column.
If you have it in a new column, try to do again but add only one criteria at the time.
=CALCULATE(sum([Order Prd Amt]),FILTER('Brand Data',[Department]=EARLIER([Department])))
if it is working add one more
=CALCULATE(sum([Order Prd Amt]),FILTER('Brand Data',[Market]=EARLIER([Market])&&[Department]=EARLIER([Department])))
and so on.
then you can find out which column give you the error.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 20 May 2019, 19:41Delegate Tsudoi said...
RE: DAX formula
Hi Jens,
Thanks again.
I've managed to get the formula in column working now, as follows.
=CALCULATE(sum([Order Prd Amt]),FILTER('Brand Data',[Year]=EARLIER([Year])&&[Month No]=EARLIER([Month No])&&[Market]=EARLIER([Market])&&[Department]=EARLIER([Department])))
However, I am not sure what you mean by
In the measure area create create a measure:
Brand%/Market:=[Department Total]/[Market Total]
How do I calculate [Department Total] and [Market Total]...?
Is it a measure I have to do, how?
And if I wanted Brand%/Market, shouldn't it be [Brand Total]/[Market Total]..?
Sorry for questions but I am a little confused here.
Tsudoi
Mon 20 May 2019, 20:25Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
If you have got what you want you will not need to do anymore.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 20 May 2019, 21:38Delegate Tsudoi said...
RE: DAX formula
Hi Jens,
I really appreciate your professional reply every time but it's not yet quite what I wanted. I wanted Brand%/Dept and Brand%/Market.
The formula returns values and I guess I need to create two measures as you initially suggested, so that I can show them in my pivot table.
From having the formula in a column, what is the measure calculation that I still need to do to achieve this?
I know it's a simple one but I just cannot get my head around it.
Much appreciated.
Tsudoi
Mon 20 May 2019, 22:24Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
If these are the final calculations you want, you will in the measure area under the table have to do two measures:
Third to get Brand%/Dept
=[@[Brand Total]]/[@[Dept Total]]
Last to get Brand%/Market
=[@[Brand Total]]/[@[Market Total]]
Brand%/Dept:=["the heading of the column calculation where you calculated brand total"]/["the heading of the column calculation where you calculated Dept total"]
Brand%/Market:=["the heading of the column calculation where you calculated brand total"]/Sum([sales])
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 21 May 2019, 15:56Delegate Tsudoi said...
RE: DAX formula
Hi Jens,
I think I’ve got that worked out finally (although have not really got my head around the logic of how EARLIER functions..)
Anyway, appreciated your very professional Q&A exchanges as always!
Tsudoi
Tue 21 May 2019, 20:41Trainer Jens said...
RE: DAX formula
Hi Tsudoi,
It is good to know it is working.
The Earlier function is a special function and I agree that the logic is not easy and the name does not make to much sense. Well may be it does, but not in the context in your example.
If you have sales records and want to find out when you had the previous sales to a specific client it can find closest earlier date.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 28 May 2019: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Formula for last day of monthIn some cases it is necessary to find the last day of a month for a given date. If you use the following formula, you can achieve this, ie; if you have a column of dates, use this formula to find the end of month for each day by using the fill handle. The formula is as follows, and assumes in this example that the first date in question is in cell C5, in any other cell type; =DATE(YEAR(C5),MONTH(C5)+1,1)-1 |
