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 Powerpoint Training and help » Mode in PowerPivot
Mode in PowerPivot
Resolved · Low Priority · Version 365
Wed 24 Jul 2019, 17:22Delegate Laurence said...
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
Wed 24 Jul 2019, 18:56
Edited on Wed 24 Jul 2019, 18:57Trainer Jens said...
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
Thu 25 Jul 2019, 11:23Delegate Laurence said...
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
Fri 26 Jul 2019, 11:53Trainer Jens said...
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
Fri 26 Jul 2019, 11:57Delegate Laurence said...
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
Fri 26 Jul 2019, 12:42Trainer Jens said...
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
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. |
PowerPoint tip:Preventing slide content changeIf you have creating a presentation and someone else is delviering it, you may want to work the presentation so that the slide content cannot be changed. |
