Laurence has attended:
Excel PowerPivot course
Mode in PowerPivot
Hi,
I'm trying to figure out how to get a Mode formula into Dax.
What i'm trying to find out relates to the margins used for Products - i've got 9 years worth of data and we're trying to pinpoint when the margins changed. So what i have is a list of transactions, with different products and the margin used for each transaction, along with the date of the transaction.
I need find the most common used per month (rather than the average, as there are a few outliers and refunds that'll throw this off).
How would you suggest approaching this? I'll need to be able to slice the margin via some related tables (customer type - new or old and product groupings)
Thanks
Laurence
RE: Mode in PowerPivot
Hi Laurence,
Thank you for the forum question.
And it is a very interesting question. You will need to create a new column in your transaction table.
You will need to calculate the frequency for each margin. Type the DAX below in the column:
=CALCULATE(COUNT([margin]),FILTER(ALL('the name of your transaction table'),[margine]=EARLIER([margin])))
The Earlier function is a strange function to explain but very powerful for a lot of things.
Next step is to make a measure down in the calculation area.
Mode:=minx(topn(1,'name of your transaction table',[frequency],0),[margin])
Please let me know if it is not what you expected or if you have questions to my answer
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
RE: Mode in PowerPivot
Thanks Jens, That didn't quite work for me, I used a Mode formula with Arrays in Excel on a small sample of the data and i was getting a differnt answer
I did find however this website - https://www.daxpatterns.com/statistical-patterns/ & the Mode formula on there does tally up with the answers i was getting on the small sample in Excel, so i've gone ahead with that one.
Thanks for your help
Laurence
RE: Mode in PowerPivot
Hi Laurence,
I am a little bit surprised that my idea didn't work. I looked at the link (thanks for adding the link) and the logic is the same as my suggestion.
Well the important thing is that you got it to work.
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
RE: Mode in PowerPivot
Thanks Jens - I guess i may have made a mistake when inputting your formula, as i agree they do look to be similar, but i was getting different results from the two approaches.
If it helps for me to send the spreadsheet two you, i've still go both formulas in there i'm happy to do so
RE: Mode in PowerPivot
Thanks Laurence,
I am just happy that you have got what you want. The logic in DAX can sometimes be hard to get right.
I created a small data model to test my idea and when I have time, I will have a closer look at your link.
Good luck with your DAX and a great weekend.
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
