- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculating the Average of a group of Calculat...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

10-25-2023
02:59 PM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-25-2023
03:39 PM

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

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-25-2023
05:54 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-26-2023
09:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-25-2023
03:39 PM

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

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-26-2023
09:46 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-26-2023
08:44 PM

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

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

113 | |

107 | |

106 | |

91 | |

67 |

Top Kudoed Authors

User | Count |
---|---|

162 | |

133 | |

133 | |

93 | |

90 |