与切片器相关的Distinct计数的运行/累积平均值

Question

我试图获得一个字段在切片器设置的时间段内的不同计数的运行/累积平均值。由于很难找到这个特定的解决方案，我一直在努力只获得不同计数的运行/累积总数，但我尝试过的每个解决方案都不起作用。

我尝试过但没有成功的解决方案：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

这会创建一个没有日期的合计行，但运行的合计不是运行的合计，而是相同的不同计数。

Power BI中的Running Total快速度量值仅返回不同的计数。

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

我也尝试了以下操作，但它在每个数据点都返回40-41之间：

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

请参阅下面的图表中使用的数据。

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

几列是度量。它们的定义如下：

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

Answer 1

问题是图表中每个点的日期都起到了过滤器的作用，阻止了运行总数的工作。如果您将结果更改为：

VAR Result =
IF (
FirstVisibleDate <= LastDateWithOrders,
CALCULATE (
iDashUndAndComProcedure[Unique Order Count],
ALL(iDashUndAndComProcedure[Settlement Date]),
iDashUndAndComProcedure[Settlement Date] <= LastVisibleDate 
)
)

它将在结算日期清除过滤器，并返回正确的值。