topic Re: DAX: formula to get minimum value from the measure in DAX Commands and Tips

DAX: formula to get minimum value from the measure

rhoex — Fri, 02 Apr 2021 16:19:03 GMT

Hi Team!

I would like to request you for the help with formula to get minimum value from the measure.

As a final result I would like to calculate the difference from minimum PO Price from all companies.

In Excel it's easy and looks like:

Excel screen link

However in PowerBI I stuck with getting that minimum value 0,8333 for all companies.

My results by error and trial there are following and nothing from them are correct.

Visualization has 3 Slices, which should be taken into account: Period, Reuse Flag and Generic SKU

PowerBI screen link

PO Price =

DIVIDE(

SUM('Data_17_21_table'[PO Amount]),

SUM('Data_17_21_table'[PO Qty])

)

My trials with minimum value:

Min1 =

minx(VALUES(Data_17_21_table),[PO Price])

Min2 =

MINX(

'Data_17_21_table',

CALCULATE([PO Price])

)

Min3 =

MINX(

KEEPFILTERS(VALUES('Data_17_21_table'[FinYear])),

CALCULATE([PO Price])

)

Min4 =

MINX(

KEEPFILTERS(VALUES('Data_17_21_table'[BU])),

CALCULATE([PO Price])

)

Min5 = minX(Data_17_21_table, DIVIDE(

SUM('Data_17_21_table'[PO Amount]),

SUM('Data_17_21_table'[PO Qty])

))

Min6 = CALCULATE(minx(VALUES(Data_17_21_table),[PO Price]), ALLEXCEPT(Data_17_21_table, Data_17_21_table[Reuse Flag], Data_17_21_table[Period], Data_17_21_table[Generic Sku]))

Min7 = CALCULATE(minx(VALUES(Data_17_21_table),[PO Price]), ALL(Data_17_21_table))

Min8 = CALCULATE(minx(VALUES(Data_17_21_table),[PO Price]), ALLCROSSFILTERED(Data_17_21_table))

Min9 =

CALCULATE(MINX(VALUES(Data_17_21_table),[PO Price]),ALLEXCEPT(Data_17_21_table, Data_17_21_table[Generic Sku], Data_17_21_table[Period], Data_17_21_table[Reuse Flag]))

Could you suggest another approach?

Thank you in advance!

rhoex

Re: DAX: formula to get minimum value from the measure

Anonymous — Fri, 02 Apr 2021 18:20:32 GMT

Hi ,

Replace the column with measures. Also use allexcept to remove effect of all filters except the ones the you want to apply (you have to write the three filter columns in all except ).

M_Final = DIVIDE([M_SumProfit],CALCULATE(MIN(Sheet1[Profit]),ALL(Sheet1)),0)-1

Re: DAX: formula to get minimum value from the measure

rhoex — Fri, 02 Apr 2021 19:10:04 GMT

Thanks Anonymous

Did you mean something like this?

M_Final_1 = DIVIDE([PO Price],CALCULATE(MIN(Data_17_21_table[PO Price]),ALL(Data_17_21_table)),0)-1

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

and with allexcept, the same error

M_Final_2 = DIVIDE([PO Price],CALCULATE(MIN(Data_17_21_table[PO Price])ALLEXCEPT(Data_17_21_table, Data_17_21_table[Generic Sku], Data_17_21_table[Period], Data_17_21_table[Reuse Flag])),0)-1

So I understand for measure I should use MINX function, and:

M_Final_3 = DIVIDE([PO Price],CALCULATE(MINX(VALUES(Data_17_21_table),[PO Price]),ALLEXCEPT(Data_17_21_table, Data_17_21_table[Generic Sku], Data_17_21_table[Period], Data_17_21_table[Reuse Flag])),0)-1

But this returns values not as I would expect. Actually it's similar approach as in Min9 above, so no proper minimum value taken 😞

Re: DAX: formula to get minimum value from the measure

rhoex — Fri, 02 Apr 2021 19:14:00 GMT

I forgot to mention and maybe this is important. PowerBI screen is showing already visualisation in Matrix. This is not table from Data. In Data_17_21_table I have much more transactions and in Matrix I am summarising this per Company

Re: DAX: formula to get minimum value from the measure

daxer-almighty — Fri, 02 Apr 2021 22:28:49 GMT

[Diff from MIN] = // ISINSCOPE guarantees that the visual // you use the measure in uses T[Company] // for grouping. Please DO NOT use this // measure in other measures, especially // those that include any explicit iterator. // THIS IS A TOP-LEVEL MEASURE. IF( ISINSCOPE( T[Company] ), var vMinPOPrice = MINX( ALLSELECTED( T[Company] ), [PO Price] ) var vResult = // This first DIVIDE makes sure BLANK // is returned when vMinPOPrice is 0. DIVIDE( vMinPOPrice, vMinPOPrice ) * ( DIVIDE( [PO Price], vMinPOPrice ) - 1 ) RETURN vResult )

Re: DAX: formula to get minimum value from the measure

Anonymous — Sat, 03 Apr 2021 03:45:39 GMT

Hi rhoex ,

I believe you have defined "M_Final_1 = DIVIDE([PO Price],CALCULATE(MIN(Data_17_21_table[PO Price]),ALL(Data_17_21_table)),0)-1"
[PO Price]
as a measure. Can you please confirm that you have used the following pattern:

M_Final_1 {Required Measure's Name }= DIVIDE({Measure of PO Price's Sum (or any other aggregation suiting your requirements},CALCULATE(MIN({Specificy PO Price Column here}),ALL(Data_17_21_table)),0)-1

Re: DAX: formula to get minimum value from the measure

rhoex — Sat, 03 Apr 2021 09:10:39 GMT

Thank you daxer-almighty !

It is working as intented. 👍

rhoex