1
votes

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

1 Answers

0
votes

The issue is the date in each point of the chart is acting as a filter, preventing the running total to work. If you change your result to:

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

It will clear the filters on Settlement Date and return you the correct value.