PDA

View Full Version : Complicated CALCULATE DAX Expression Help

sieplexus
2016-07-27, 04:54 PM
Hi,

Although quite 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:

=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:

=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 within the given range.

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!

MikeManwaring
2016-08-04, 09:17 AM
Hi

The calculate looks good, does it not provide the same answer if E17 = 500 on the sumif?
Maybe an example workbook might help, do you have one?

Mike