Calculating the Average of a group of Calculated Minimum values from a dataset

[Bigjogle]

Hello all,

First time posting here but I have been delving through the forums and have not found anyone doing quite what I am trying to accomplish.

I have a folder that is being populated with log files, each log file contains run data for one run (Repetition) of an experiment setup (DOE #).

I would like to calculate the minimum value for "Load" for each "run" (Repetition) and then calculate the average of these minimums based on experiment setup (DOE #). 

Here is a link to the dataset: https://drive.google.com/file/d/1phSWp27iRWmjp0khJq49hrQ7zp0ekosQ/view?usp=sharing

Here is a link to my current pbix: https://drive.google.com/file/d/1T-Ds1G7f6K2UMuxiOiOo7OxluuGB00Ws/view?usp=sharing

Any help would be greatly appriciated!

-John

[gmsamborn]

Hi @Bigjogle 

I did this with 2 measures.  I'm sure it can be done in 1 but this seems simpler.

zMin = 
    CALCULATE(
        MIN( 'Log'[Load (lbf)] ),
        ALLEXCEPT( 'Log', 'Log'[DOE #], 'Log'[Repetition] )
    )

zAvg = 
    AVERAGEX(
        ALLSELECTED( 'Log'[Repetition], 'Log'[Reading] ),
        [zMin]
    )

I hope this helps.

[Bigjogle]

This method also is a solution to the problem and thank you for your time!

Interestingly, the two solutions that are currently here give slightly  different results (likely due to how many digits went into the calculations). 
One solution gives 0.85, the other 0.84. If I manually calculate based on the 2 decimal numbers I get 0.845 (so if one solution was even off by 0.001 it could be swayed in either direction!)

Not a problem for my case, I just thought it was interesting.

[TomMartens · Accepted solution]

Hey @Bigjogle ,

I assume this measure returns what you are looking for:

average of min values = 
SELECTCOLUMNS(
    GROUPBY(
        SELECTCOLUMNS(
            ADDCOLUMNS(
                CALCULATETABLE(
                    SUMMARIZE(
                        'Log'
                        ,   'Log'[DOE #]
                        ,   'Log'[Repetition]
                    )
                    , ALL( 'Log'[Repetition] )
                )
                , "minV" , [Min Value]
            )
            , "doe #" , [DOE #]
            , "minV" , [minV]
        )
        , [doe #]
        , "avg_minV" , AVERAGEX( CURRENTGROUP() , [minV] )
    )
    , "value" , [avg_minV]
)

At least the values look like the expected ones:

Hopefully, this provides what you are looking for.

Regards,

Tom

[Bigjogle]

Thank you so much for the very rapid response. This worked great!

[TomMartens]

Hey @Bigjogle , Hey @gmsamborn ,

I consider calculating the average value one of the most challenging tasks in DAX. The reason for this is reflecting on the number of rows or - the divisor. When I adjust my solution a little and visualize the divisor like this:

average of min values = 
countrows(
    // GROUPBY(
        SELECTCOLUMNS(
            ADDCOLUMNS(
                CALCULATETABLE(
                    SUMMARIZE(
                        'Log'
                        ,   'Log'[DOE #]
                        ,   'Log'[Repetition]
                    )
                    , ALL( 'Log'[Repetition] )
                )
                , "minV" , [Min Value]
            )
            , "doe #" , [DOE #]
            , "minV" , [minV]
        )
    //     , [doe #]
    //     , "avg_minV" , AVERAGEX( CURRENTGROUP() , [minV] )
    // )
    // , [avg_minV]
)

and @gmsamborn solution like so:

zAvg = 
    countrows(
    // AVERAGEX(
        ALLSELECTED( 'Log'[Repetition], 'Log'[Reading] )
    //     [zMin]
    )

There are completey different results:

This will also explain the different "accuracy" when changing the number of decimals. And by writing this i realized that my initial DAX was not the solution I intended to provide, this is the one I wanted to share:

average of min values__ = 
AVERAGEX(
    GROUPBY(
        SELECTCOLUMNS(
            ADDCOLUMNS(
                CALCULATETABLE(
                    SUMMARIZE(
                        'Log'
                        ,   'Log'[DOE #]
                        ,   'Log'[Repetition]
                    )
                    , ALL( 'Log'[Repetition] )
                )
                , "minV" , [Min Value]
            )
            , "doe #" , [DOE #]
            , "minV" , [minV]
        )
        , [doe #]
        , "avg_minV" , AVERAGEX( CURRENTGROUP() , [minV] )
    )
    , [avg_minV]
)

The difference becomes obvious when the property "Total" is turned on again for the table data visualizaion:

@Bigjogle hopefully this additional context helps to understand the different results. For this specific use case I favor my appraoch. But as always, it depends 🙂 This is the reason why I bother my colleagues with an exact definition of the divisior (the number of rows) when I'm getting asked to help with calculating an average.

Regards,

Tom