Minimum of average over partition by in DAX

[BK1234]

Hello,

In a tabular model I have a table with collumns like date, name, type and NumberOfDays (integer). The date-collumn is hided because there is another table period, with the data, month and year.

There are 6 different namens and 2 different types. Every combination of name/type has at most one value (NumberOfDays) for every monday.

Example:

Date             Name   Type   NumberOfDays

20190107     abc       1         20

20190107     abc       2         12

20190114     abc       1         2

20190114     def        1         4

20190121     abc       1         8

20190121     def       1         11

20190121     ghi       2         9

In my pbix I have slicers for year, month and type. 

What I want is to display one value, which is computed as follows:

- calculate for every name, with respect to the given filtervalues, the average NumberOfDays.

- display the lowest average of the 6 names. Names that have average = blank, don't need to be taken into account.

In SQL I could do this with something like average over partition by and than take the minimum. But in Power BI nothing works. Because my source is a tabular model, I can't add columns or tables in my pbix.

Who can help me?

[Anonymous]

for your first bullet, if you put Name on rows, and all your other filters as slicers and what not, i think you could just use 

Avg Days = 
AVERAGE( Table2[NumberOfDays] )

I dont quite follow your second bullet point though.

[BK1234]

Hi Nick,

Thanks for your reply!

My second bullet is the one I can't get working. In your example you have the averages per name. What I want is to display the minimum of the list with averages, as a single value. In the example, the minimum of the firts list with averages, is 7.50 and in the second list it's also 7.50.

Is this  more clear?