topic Re: DAX: formula to reflect Excel’s MINIFS in DAX Commands and Tips

DAX: formula to reflect Excel’s MINIFS

rhoex — Wed, 07 Apr 2021 22:17:48 GMT

Hi supporting team!

I would like to request for the help with preparing formula for the measure to calculating variance from minimum price value (BU Var%), but not from whole population just for specific criterion (BU).

Please see below screen with data and formula in Excel.

Thanks to daxer-almighty I have formula in Power BI for calculating the variance (Upcharge column in Excel with example formula: =O2/MIN($O$2:$O$14)-1) for the whole population, keeping current filter in the slicers, but taking into account that PO Price is per BU, Company, Vendor Name):

Upcharge =

IF( ISINSCOPE( Data_17_21_table[Vendor Name]),

var vMinPOPrice =

MINX(

ALLSELECTED( Data_17_21_table[BU], Data_17_21_table[Company], Data_17_21_table[Vendor Name] ),

[PO Price] )

var vResult =

DIVIDE( vMinPOPrice, vMinPOPrice )

* ( DIVIDE( [PO Price], vMinPOPrice ) - 1 )

RETURN

vResult)

This is proper one and taking minimum PO Price from all BUs. In new value I need minimum value, but only from the same BU. Could you help?

Thank you,

rhoex

Re: DAX: formula to reflect Excel’s MINIFS

wdx223_Daniel — Thu, 08 Apr 2021 00:51:35 GMT

divide(max(table[po price]),calculate(min(table[po price]),filter(allselect(table),table[bu]=max(table[bu]))))

Re: DAX: formula to reflect Excel’s MINIFS

rhoex — Thu, 08 Apr 2021 10:04:06 GMT

Thanks wdx223_Daniel ,

I've translated your suggestion into:

BU Var% =

divide(max(Data_17_21_table[PO Price], calculate(min(Data_17_21_table[PO Price]), filter(allselect(Data_17_21_table), Data_17_21_table[BU]=max(Data_17_21_table[BU])))))

but I've got error:

Column 'PO Price' in table 'Data_17_21_table' cannot be found or may not be used in this expression.

I forgot to mention that 'PO Price' is already a measure. It's formula is:

PO Price =

DIVIDE(

SUM('Data_17_21_table'[PO Amount]),

SUM('Data_17_21_table'[PO Qty])

)

rhoex

Re: DAX: formula to reflect Excel’s MINIFS

wdx223_Daniel — Fri, 09 Apr 2021 07:44:16 GMT

divide([PO Price],minx(filter(allselect(Data_17_21_table), Data_17_21_table[BU]=max(Data_17_21_table[BU])),[PO Price]))

Re: DAX: formula to reflect Excel’s MINIFS

rhoex — Sun, 11 Apr 2021 21:00:23 GMT

Thank you wdx223_Daniel ,

Currently I am receiving an error:

Failed to resolve name 'allselect'. It is not a valid table, variable, or function name.

Could you help?

Thx,

rhoex

Re: DAX: formula to reflect Excel’s MINIFS

wdx223_Daniel — Mon, 12 Apr 2021 00:30:32 GMT

it should be ALLSELECTED instead of allselect

Re: DAX: formula to reflect Excel’s MINIFS

rhoex — Mon, 12 Apr 2021 11:23:51 GMT

Thank you wdx223_Daniel !

I think we are almost there. Just the formula is not taking something into account, because still as a reference value (second part in divide formula) is taking not minumum PO Price from the specific BU. There are no hidden filters, just slicered value and is not showing proper minumum price.

Please look into results:

Last column (BU Var% 2) should show minimim price from the specific BU. In first instance should be seen 0.0908 not 0.885, in second 0.0900 not 0.885, in third 0.841 not 0.820.

The formula for this is:

BU Var% 2 =
minx(filter(allselected(Data_17_21_table), Data_17_21_table[BU]=max(Data_17_21_table[BU])),[PO Price])

Maybye it's not taking into account slicers filters (Generic SKU, Reuse Flag, Period - Last 12M and Period) (?)

rhoex

Re: DAX: formula to reflect Excel’s MINIFS

rhoex — Wed, 14 Apr 2021 10:12:31 GMT

Does anyone have an idea what I could change in the formula?

Thank you in advance

rhoex

Re: DAX: formula to reflect Excel’s MINIFS

daxer-almighty — Fri, 23 Apr 2021 11:12:36 GMT

Hi rhoex

Have you finally solved the puzzle?

Re: DAX: formula to reflect Excel’s MINIFS

rhoex — Fri, 23 Apr 2021 11:14:39 GMT

Hi daxer-almighty Unfortunatley not 😞 Still waiting for some advice.

Re: DAX: formula to reflect Excel’s MINIFS

daxer-almighty — Fri, 23 Apr 2021 11:23:44 GMT

// Something like this? [BU Var%] = IF( ISINSCOPE( Data_17_21_table[Vendor Name] ) && ISINSCOPE( Data_17_21_table[BU] ), var vCurrentBU = SELECTEDVALUE( Data_17_21_table[BU] ) VAR vMinPOPrice = MINX( FILTER( ALLSELECTED( Data_17_21_table[BU], Data_17_21_table[Company], Data_17_21_table[Vendor Name] ), Data_17_21_table[BU] = vCurrentBU ), [PO Price] ) VAR vResult = DIVIDE( vMinPOPrice, vMinPOPrice ) * ( DIVIDE( [PO Price], vMinPOPrice ) - 1 ) RETURN vResult )

Re: DAX: formula to reflect Excel’s MINIFS

rhoex — Fri, 23 Apr 2021 13:29:14 GMT

Yes! It's working properly.

Thank you daxer-almighty !

rhoex