mode powerpivot

Forum home » Delegate support and help forum » Microsoft Powerpoint Training and help » Mode in PowerPivot

Mode in PowerPivot

resolvedResolved · Low Priority · Version 365

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

Edited on Wed 24 Jul 2019, 18:57

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

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

PowerPoint tip:

Preventing slide content change

If 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.

To do this you can convert the slides into a PNG Portable Network Graphics Format (better than others -takes less memory up). Click on saVae as and change the save as type to PNG.
You will then be given the option to save the slide you are on, or all slides.

Create a new presentation and then Insert the saved PNG files into the new presentation. This way no one can change your slides, the way they were meant to be

View all PowerPoint hints and tips


Server loaded in 0.11 secs.