Usage per day by periods

[ambisset]

I'm new to Power BI so learning the syntax and capabilities of DAX is a work in progress. I've searched for what I'm trying to do but not finding a similar solution.

I work for a harbour and within the harbour we have a number of berths. I have successfully created from our raw data a table of berth usages. This has these relevant fields...

Date, BerthID, OccupiedPercentage (ie: percentage of day berth was occupied expressed as a decimal 0-1). eg:

01/01/2019, 1, .5

01/01/2019, 2, .75

02/01/2019, 1, .6

etc

I've got a table of dates with heirarchies that I've used before in other reports and that gives a drill down by year, quarter, month, week, day. That works.

When I link to the BerthUsage I'm getting the summed total of the daily percentages for that berth when what I need is the cumulative figure expressed as a percentage of the number of days that were selected.

An example may help 

If the user is viewing an entire year and is looking at berth 1 I want to see that the percentage utilisation for that berth for the year is the sum of the daily percentages/365 to give the occupancy percentage for that year. 

I am assuming I need to divide the sum of percentages by the number of days in the period block the user is looking at but I can't workout the correct syntax for the DAX measure.

Thanks in advance for your assistance.

[Greg_Deckler]

Perhaps something along the lines of:

Measure = 
VAR __table = ALLSELECTED('Table')
RETURN
SUMX(__table,[OccupiedPercentage]) / COUNTROWS(__table)

[ambisset]

Sadly that only seems to return the same value regardless of the date. I'm assuming that it should be a table level measure and not in a column?

Measure 1:= VAR __table = ALLSELECTED('Berth Usage') RETURN SUMX(__table,[BerthPercentage]) / COUNTROWS(__table)

Have I misinterpreted the formula?

If I add berths it just shows exactly the same value by berth as well. So it's as if that formula is only working on the whole table.

[ambisset]

This is what the PowerBI visualisation trial looks like...

It's picking up the relationships from the SSDT relationship model as follows. This splits by date just fine if I just use the BerthPercentage field rather than the measure however it sums it rather than showing the average for that period.

[ambisset · Accepted solution]

I've ended up using a simple average function as a measure for now it appears to do a reasonable job although it's rather crude.