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
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.
Solved! Go to Solution.
Perhaps something along the lines of:
Measure = VAR __table = ALLSELECTED('Table') RETURN SUMX(__table,[OccupiedPercentage]) / COUNTROWS(__table)
Proud to be a Datanaut!
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.
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.