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

Usage per day by periods

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ambisset Frequent Visitor
Frequent Visitor

Re: Usage per day by periods

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.

4 REPLIES 4
Super User
Super User

Re: Usage per day by periods

Perhaps something along the lines of:

 

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ambisset Frequent Visitor
Frequent Visitor

Re: Usage per day by periods

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?

image.png

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.

image.png

ambisset Frequent Visitor
Frequent Visitor

Re: Usage per day by periods

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

image.png

 

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.

image.png

ambisset Frequent Visitor
Frequent Visitor

Re: Usage per day by periods

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.