cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

New Member

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

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 #).

Any help would be greatly appriciated!

-John

1 ACCEPTED SOLUTION
Super User

Hey @Bigjogle ,

I assume this measure returns what you are looking for:

``````average of min values =
SELECTCOLUMNS(
GROUPBY(
SELECTCOLUMNS(
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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
5 REPLIES 5
Super User

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

``````zMin =
CALCULATE(
ALLEXCEPT( 'Log', 'Log'[DOE #], 'Log'[Repetition] )
)

zAvg =
AVERAGEX(
[zMin]
)``````

I hope this helps.

New Member

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.

Super User

Hey @Bigjogle ,

I assume this measure returns what you are looking for:

``````average of min values =
SELECTCOLUMNS(
GROUPBY(
SELECTCOLUMNS(
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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
New Member

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

Super User

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(
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(
//     [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(
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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany