Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rhoex
Regular Visitor

DAX: formula to get minimum value from the measure

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 

Capture.JPG

 

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 

Capture2.JPG

 

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

1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

rhoex
Regular Visitor

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

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

Thank you @daxer-almighty ! 

It is working as intented. 👍

 

rhoex

Anonymous
Not applicable

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

 

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 😞

Capture3.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors