DAX: formula to reflect Excel’s MINIFS

[rhoex]

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

[wdx223_Daniel]

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

[wdx223_Daniel]

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

[rhoex]

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

[wdx223_Daniel]

it should be ALLSELECTED instead of allselect

[rhoex]

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

[rhoex]

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

Thank you in advance 

rhoex

[daxer-almighty]

Hi @rhoex 

Have you finally solved the puzzle?

[daxer-almighty · Accepted solution]

// 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
)

[rhoex]

Yes! It's working properly.

Thank you @daxer-almighty !

rhoex

[rhoex]

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

[rhoex]

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