I'm trying to get running/cumulative average of a distinct count of a field over a time period set by slicers. Since finding this specific solution has been difficult, I've been working toward just getting a running/cumulative total of the distinct count but every solution I've tried there has not been working.
Solutions that I've tried without success: https://www.daxpatterns.com/cumulative-total/#
Unique Orders RT =
VAR LastVisibleDate =
MAX ( iDashUndAndComProcedure[Settlement Date] )
VAR FirstVisibleDate =
MIN ( iDashUndAndComProcedure[Settlement Date] )
VAR LastDateWithOrders =
CALCULATE (
MAX ( iDashUndAndComProcedure[Settlement Date] ),
REMOVEFILTERS () -- Use ALL ( Sales ) if REMOVEFILTERS () and ALL ()
-- are not available
)
VAR Result =
IF (
FirstVisibleDate <= LastDateWithOrders,
CALCULATE (
iDashUndAndComProcedure[Unique Order Count],
iDashUndAndComProcedure[Settlement Date] <= LastVisibleDate
)
)
RETURN
Result
This creates a total row without a date but the running total isn't a running total, it's the same distinct count.
The Running-Total quick measure in Power BI just returns the distinct count.
Unique Order Count running total in Settlement Date =
CALCULATE(
[Unique Order Count],
FILTER(
ALLSELECTED('iDashUndAndComProcedure'[Settlement Date]),
ISONORAFTER('iDashUndAndComProcedure'[Settlement Date], MAX('iDashUndAndComProcedure'[Settlement Date]), DESC)
)
)
I tried the following as well but it returns between 40-41 at every data point:
Average Clearance Officer Assignments Unique Cumulative =
CALCULATE( [Average Assignments for Unique Orders per Clearance Officer], FILTER( ALLSELECTED( iDashUndAndComProcedure ), iDashUndAndComProcedure[Order Number Only] < max( iDashUndAndComProcedure[Order Number Only] )))
See below for the data being utilized in the chart.
<table class="tg">
<thead>
<tr>
<th class="tg-0lax">Year</th>
<th class="tg-0lax">Month</th>
<th class="tg-0lax">Orders (including A,B,C,etc. Portfolio files)</th>
<th class="tg-0lax">Unique Orders</th>
<th class="tg-0lax">Average Assignments per Clearance Officer</th>
<th class="tg-0lax">Average Assignments for Unique Orders per Clearance Officer</th>
<th class="tg-0lax">Count of Clearance Officer</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tg-0lax">2019</td>
<td class="tg-0lax">September</td>
<td class="tg-0lax">122</td>
<td class="tg-0lax">107</td>
<td class="tg-0lax">6.777777777777797</td>
<td class="tg-0lax">5.944444444444454</td>
<td class="tg-0lax">18</td>
</tr>
<tr>
<td class="tg-0lax">2019</td>
<td class="tg-0lax">October</td>
<td class="tg-0lax">210</td>
<td class="tg-0lax">107</td>
<td class="tg-0lax">11.666666666666659</td>
<td class="tg-0lax">5.94444444444443</td>
<td class="tg-0lax">18</td>
</tr>
<tr>
<td class="tg-0lax">2019</td>
<td class="tg-0lax">November</td>
<td class="tg-0lax">198</td>
<td class="tg-0lax">117</td>
<td class="tg-0lax">11</td>
<td class="tg-0lax">6.5</td>
<td class="tg-0lax">18</td>
</tr>
<tr>
<td class="tg-0lax">2019</td>
<td class="tg-0lax">December</td>
<td class="tg-0lax">262</td>
<td class="tg-0lax">183</td>
<td class="tg-0lax">13.099999999999941</td>
<td class="tg-0lax">9.150000000000048</td>
<td class="tg-0lax">20</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">January</td>
<td class="tg-0lax">147</td>
<td class="tg-0lax">130</td>
<td class="tg-0lax">4.899999999999987</td>
<td class="tg-0lax">4.333333333333336</td>
<td class="tg-0lax">30</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">February</td>
<td class="tg-0lax">176</td>
<td class="tg-0lax">122</td>
<td class="tg-0lax">8</td>
<td class="tg-0lax">5.545454545454527</td>
<td class="tg-0lax">22</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">March</td>
<td class="tg-0lax">113</td>
<td class="tg-0lax">109</td>
<td class="tg-0lax">4.708333333333333</td>
<td class="tg-0lax">4.541666666666669</td>
<td class="tg-0lax">24</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">April</td>
<td class="tg-0lax">98</td>
<td class="tg-0lax">81</td>
<td class="tg-0lax">4.083333333333329</td>
<td class="tg-0lax">3.375</td>
<td class="tg-0lax">24</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">May</td>
<td class="tg-0lax">141</td>
<td class="tg-0lax">114</td>
<td class="tg-0lax">7.049999999999981</td>
<td class="tg-0lax">5.700000000000013</td>
<td class="tg-0lax">20</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">June</td>
<td class="tg-0lax">130</td>
<td class="tg-0lax">121</td>
<td class="tg-0lax">6.5</td>
<td class="tg-0lax">6.049999999999988</td>
<td class="tg-0lax">20</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">July</td>
<td class="tg-0lax">117</td>
<td class="tg-0lax">99</td>
<td class="tg-0lax">6.15789473684209</td>
<td class="tg-0lax">5.210526315789479</td>
<td class="tg-0lax">19</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">August</td>
<td class="tg-0lax">126</td>
<td class="tg-0lax">117</td>
<td class="tg-0lax">6.299999999999986</td>
<td class="tg-0lax">5.850000000000015</td>
<td class="tg-0lax">20</td>
</tr>
<tr>
<td class="tg-0lax">2020</td>
<td class="tg-0lax">September</td>
<td class="tg-0lax">177</td>
<td class="tg-0lax">127</td>
<td class="tg-0lax">9.83333333333331</td>
<td class="tg-0lax">7.055555555555568</td>
<td class="tg-0lax">18</td>
</tr>
</tbody>
</table>A couple columns are measures. They are defined below:
Average Assignments for Unique Orders per Clearance Officer = AVERAGEX(iDashUndAndComProcedure, DIVIDE(DISTINCTCOUNT(iDashUndAndComProcedure[Order Number Only]), DISTINCTCOUNT(iDashUndAndComProcedure[Clearance Officer])))
Average Assignments per Clearance Officer = AVERAGEX(iDashUndAndComProcedure, DIVIDE(DISTINCTCOUNT(iDashUndAndComProcedure[Order Number]), DISTINCTCOUNT(iDashUndAndComProcedure[Clearance Officer])))
