Help converting Excel SumIF into DAX

[Plexus]

Hi There,

I am totally new to PowerPivot and have being trying to create a SumIf Excel formula.

The Excel formula is:

=SUMIF($E$8:$E$736,">="&E17,$E$8:$E$736)/SUM($E$8:$E$736)

The formula works out a % of sales volume that I then use to apply a grade.

I have been trying to use CALCULATE and FILTER but am unable to achieve the required results.

I appreciate any help that can be given or perhaps point me in the direction of what DAX formula would be best to use.

Thanks.

 

[Matt Allington]

You will struggle with the approach you are taking. Power Pivot is within the reach of any Excel user that can write SUMIF, but you need to properly learn how Power Pivot works before you try to write formulas like this. I can't really teach you all you need to know in a forum post. I suggest you read my book for a fast and effective way to Power Pivot success. Learn to Write DAX - Excelerator BI You won't be sorry.

 

[Plexus]

Hi,

Thanks for suggesting I read your book Matt, however I do not have time for that at the moment. Although new to DAX I do learn quickly and believe the Excel formula I am trying to convert to DAX to be an incredibly complicated task, one that is causing even experienced DAX writers a headache, because as yet on various forums no one has been able to solve this question, despite many attempts!

I started off by writing the expression:

[FONT=&quot]=CALCULATE(SUM(DATA[VOLUME]),FILTER('DATA',DATA[VOLUME]>=500))/SUM(DATA[VOLUME])

as this is the closest I can get to it, as you will notice the >= 500 part is incorrect as the original Excel formula states:

[/FONT]=SUMIF($E$8:$E$736,">="&E17,$E$8:$E$736)/SUM($E$8:$E$736) so the SUMIF criteria is to only sum up values equal and greater than the cell value.

This is the part of the formula that is causing problems for anyone attempting to solve this and the reason I am back again asking here.

Anyone any ideas how I can overcome the SUMIF criteria problem?

Many thanks for any help, I live in hope!

 

[Matt Allington]

if you produce a simple excel workbook with some sample data and a working version of your sumif formula and link it here, I will take a look.

 

[Plexus]

if you produce a simple excel workbook with some sample data and a working version of your sumif formula and link it here, I will take a look.

Hi Matt,

I have created a sample workbook as requested. https://1drv.ms/x/s!AmV520RNN0VGgkoWNcrO_sQPzp6D The Formula is in column E 'Grades'

I would be very grateful if you could work out this problem or at least tell me if this will be possible in DAX.

Many thanks,

Plex.

 

[Matt Allington]

Ahhh, you are trying to do ABC Classification on your products! It's amazing what a good sample workbook can do for clarity . I could work this out for you, but the problem has already been solved and shared by The Italians. Take a look at this. ABC Classification – DAX Patterns

If I was to give you a solution, I would simply read the above post and then work out how to apply it. How about you give it a go. It can definitely be done.

Also, btw. I assume that you have different product families. It is really important in your test data that you make it realistic. In your test data, there is only 1 product family. You really should create a second product family in your test data, otherwise you risk creating a solution that works on this test data, but fails with production data.

If you can't work it out, post back and I will help you again.

 

[Plexus]

Hi Matt,

Thanks so much for providing me with the link to the solutions!

I thought I had worked this out and managed to get the correct results right up until (as you alluded to) the use of multiple product groups. I am unable to work out how to aggregate the % at SKU level, split per Product Group. Links below show examples of a correct report for single Product Group and incorrect report for multiple Product Groups.

https://1drv.ms/x/s!AmV520RNN0VGgkzsbT9B4Bo5ZAGe All Products Group report

https://1drv.ms/x/s!AmV520RNN0VGgk2Hj12b9wtcPzOI Single Product Group report


Hope you can help as I am having trouble understanding how best to overcome this problem after getting, what seemed to be really close.

Thanks,

Plex.

 

[Matt Allington]

I don't want to abandon you here, but I can't easily work out what you have done. The pattern shown at dax patterns (The link I provided) has 2 tables, a sales table and a dimension table, but that is not what you have done. So my advice is to follow the pattern exactly and don't try to take short cuts by keeping everything in 1 table when the pattern uses 2 tables. For example there is a big different between All(Table1) in your Cum % column and ALL(Products)

 

[Plexus]

Thanks Matt,

As it happens I have found the solution - I was using ALL instead of ALLEXCEPT in one of the calcs which was throwing it, all fixed now but thanks for coming back to me - much appreciated!