cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Minimum of average over partition by in DAX

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?

2 REPLIES 2
Highlighted
Anonymous
Not applicable

Re: Minimum of average over partition by in DAX

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

Simple Avg.png

 

I dont quite follow your second bullet point though.

Highlighted
Regular Visitor

Re: Minimum of average over partition by in DAX

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?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors