Multiple rollups by different aggregations in same table

[macfuller]

I am trying to develop a payment schedule rollup for our expenditures. I have a disconnected table with various payment durations and I want to total the checks that were paid in that time period.

[TABLE="width: 375"]
<tbody>[TR]
[TD="width: 95, bgcolor: transparent"]Duration
[/TD]
[TD="width: 108, bgcolor: transparent"]Jan 018
[/TD]
[TD="width: 96, bgcolor: transparent"]Feb 2018
[/TD]
[TD="width: 201, bgcolor: transparent"]etc
[/TD]
[/TR]
[TR]
[TD="width: 95, bgcolor: transparent"]Immediate
[/TD]
[TD="width: 108, bgcolor: transparent"]$100
[/TD]
[TD="width: 96, bgcolor: transparent"]$200
[/TD]
[TD="width: 201, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 95, bgcolor: transparent"]1-7 days
[/TD]
[TD="width: 108, bgcolor: transparent"]$300
[/TD]
[TD="width: 96, bgcolor: transparent"]$40

[/TD]
[TD="width: 201, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 95, bgcolor: transparent"]8-14 days
[/TD]
[TD="width: 108, bgcolor: transparent"]$400
[/TD]
[TD="width: 96, bgcolor: transparent"]$300
[/TD]
[TD="width: 201, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 95, bgcolor: transparent"]etc
[/TD]
[TD="width: 108, bgcolor: transparent"][/TD]
[TD="width: 96, bgcolor: transparent"][/TD]
[TD="width: 201, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

The difficulty is that my one-dimensional rollup measure is giving duplicate results as follows…
Each payment we make is composed of multiple vouchers. A voucher can come in any time before we cut our next check to that vendor. Our ERP reports them as:

Voucher	Actual Days to Pay	Voucher Amount	Check Number	Payment Amount

<tbody>
[TD="width: 82, bgcolor: transparent"] 001

[/TD]
[TD="width: 122, bgcolor: transparent"] 5
[/TD]
[TD="width: 122, bgcolor: transparent"] $10
[/TD]
[TD="width: 122, bgcolor: transparent"] 455
[/TD]
[TD="width: 122, bgcolor: transparent"] $30
[/TD]

[TD="width: 82, bgcolor: transparent"] 002

[/TD]
[TD="width: 122, bgcolor: transparent"] 10
[/TD]
[TD="width: 122, bgcolor: transparent"] $10
[/TD]
[TD="width: 122, bgcolor: transparent"] 455
[/TD]
[TD="width: 122, bgcolor: transparent"] $30
[/TD]

[TD="width: 82, bgcolor: transparent"] 003
[/TD]
[TD="width: 122, bgcolor: transparent"] 12
[/TD]
[TD="width: 122, bgcolor: transparent"] $10
[/TD]
[TD="width: 122, bgcolor: transparent"] 455
[/TD]
[TD="width: 122, bgcolor: transparent"] $30
[/TD]

</tbody>

Prior to the need to display the AP calendar I had a fairly standard summarization measure that I use to ensure I only total check amounts across check numbers:

[COLOR=#222222][FONT=Verdana]Check Value:=SUMX ([/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]   SUMMARIZE ( Vouchers, Vouchers[Check Number] ),[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]   CALCULATE ( MAX ( Vouchers[Payment Amount] ) )[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana])[/FONT][/COLOR]

This was my starting code, but I realized that it was assigning the check amount to EVERY duration where a voucher was listed.

[COLOR=#222222][FONT=Verdana]Actual Payment By Period:=CALCULATE ([/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]   [Check Value],[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]   FILTER([/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        Vouchers, [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        Vouchers[Actual Days to Pay] >= MIN('AP Intervals'[Min])[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]       && Vouchers[Actual Days to Pay] < MAX('AP Intervals'[Max])[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]       )[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana])[/FONT][/COLOR]

Since there are vouchers in my example that fall into both the 1-7 and 8-14 duration I’m reporting $30 in each duration. I need to ensure that the check is assigned to only one of the durations within the list of vouchers. There is some internal debate as to whether it should be an AVERAGE or MAX of the Actual Days to Pay, but either way I’m struggling to determine how I can group the measure properly. I’ve visited the SQLBI site but, as is true 90% of the time, their explanations are way over my head.

Thanks for any help you can give!

 

[Ozeroth]

Hi there,

With your Vouchers table structured the way it is, this is a possible measure, that would ensure each Check Number ends up in one segment only:

Actual Payment By Period :=
CALCULATE (
    [Check Value],
    FILTER (
        VALUES ( Vouchers[Check Number] ),
        VAR DaysToPay =
            CALCULATE ( [COLOR=#ff0000][B]MAX[/B][/COLOR] ( Vouchers[Actual Days To Pay] ) )
        RETURN
            DaysToPay >= MIN ( 'AP Intervals'[Min] )
                && DaysToPay < MAX ( 'AP Intervals'[Max] )
    )
)

or if variables aren't available

Actual Payment By Period :=
CALCULATE (
    [Check Value],
    FILTER (
        VALUES ( Vouchers[Check Number] ),
        CALCULATE ( [B][COLOR=#ff0000]MAX [/COLOR][/B]( Vouchers[Actual Days To Pay] ) )
            >= MIN ( 'AP Intervals'[Min] )
            && CALCULATE ( [COLOR=#ff0000][B]MAX [/B][/COLOR]( Vouchers[Actual Days To Pay] ) ) < MAX ( 'AP Intervals'[Max] )
    )
)

MAX could equally be changed to AVERAGE or any other aggregation.

By the way, I assume that the total of the Voucher Amount column for a given Check is always equal to the value in the Payment Amount column that appears in rows of that Check(?) If so, then would summing the Voucher Amount column ever make sense (rather than using the Payment Amount column)? I am guessing there is a reason you don't use the Voucher Amount column to split a Check among durations?

Regards
Owen

 

[macfuller]

Thanks for the response - I'll check it next week.

Good question. Yes, the sum of the Voucher Amounts should total the Payment Amount for that check. But... I don't know if other ERP systems manage vouchers the same way as PeopleSoft, but I get a hybrid query that pulls from both the General Ledger and Voucher tables. Records in the voucher file don't relate back to the purchase order line directly (?!?) and there are some other oddities. In fact, there are Unit Price * Quantity values that may be more accurate than the voucher amount because an invoice from a vendor may be allocated to multiple departments (summary invoices) that weren't identified on the original PO. For example we may have a contract to deliver anesthesia gasses with a single PO line, but we allocate the invoice internally to departments based on the number of hospital beds. It gets complicated... we're working out what the proper logic should be for deciding which metric is appropriate under what circumstances (PeopleSoft has out-of-the-box GAAP compliance in their reporting but we've got 19 years of customizations).

Another subset... Non-PO expenditures (e.g. patient refunds) have a quantity of zero so I may have to use Voucher Amount for those.

Frankly, we haven't really begun to do the kind of analysis in AP that we've done for requisitions and orders, so that's why I'm trying to see what we can do. I'm grateful for PowerPivot, but the idiosyncracies of the ERP data make everything a challenge. The report I'm asking about, once it's running, will help us answer some vital questions and build additional insights. Are we paying vendors within a duration that matches the contract? Are we getting a discount for paying earlier than our net 45 standard? How many discount opportunities are we missing? How much are we paying in 30 days without a discount in the contract that we would be willing to pay in 10 days with a discount? How much should that discount be to make it worthwhile? How heavily should the company weight cash-on-hand vs. discount savings in renegotiating contracts?

I raise these issues just in case other readers of this thread have progressed further than our company in these areas and have some insights and formulas. We don't want to be bleeding edge!