Simulated weighted average payment type

[KB88]

I am trying to use a what-if parameter to make a simulated weighted average payment term measure. I have 2 files, 1 with payment terms and the number of days associated. The other file is the main data with Vendor name, spend amount, payment term, date. I have 1000s of vendors and many of them have multiple payment terms. I have a formula for the weighted avg Payment term (WAPT) which is. This is my formula for Current Weighted average payment term

WeightedAvgPT = 
DIVIDE(
    SUMX(
        'SC Data',
         RELATED('Payment Terms'[DAYS]) * 'SC Data'[Spend]
        ),
        SUM('SC Data'[Spend])
    )

Below I have included sample data from both tables. I want to be able to simulate what would happen to the WAPT if I changed one vendors payment terms. For example, if I changed vendor B to 60 day terms.

Payment Term	Days
A030	30
A060	60
A045	45
A025	25
A035	35

Vendor Name	Spend	Payment Term	Date
Vendor A	$  150,000.00	A030	1/1/2025
Vendor A	$  200,000.00	A030	1/20/2025
Vendor A	$    30,000.00	A030	2/15/2025
Vendor A	$    50,000.00	A030	2/28/2025
Vendor A	$    60,000.00	A030	3/1/2025
Vendor A	$    15,000.00	A030	3/2/2025
Vendor B	$  500,000.00	A030	2/3/2025
Vendor B	$    20,000.00	A060	1/4/2025
Vendor B	$    15,000.00	A045	3/5/2025
Vendor C	$    30,000.00	A025	3/6/2025
Vendor C	$    32,000.00	A035	3/7/2025
Vendor D	$  100,000.00	A060	3/8/2025
Vendor D	$  100,000.00	A060	3/9/2025
Vendor D	$  120,000.00	A060	1/10/2025
Vendor D	$    20,000.00	A060	2/11/2025
Vendor D	$    75,000.00	A060	3/12/2025
Vendor D	$    80,000.00	A060	1/13/2025
Vendor E	$  175,000.00	A045	3/14/2025

Lastly, Here is a screenshot from my actual dashboard. On the left in purple those are verified numbers, with the only filters on the top 2 being the date slicer and excluding anything that has a blank payment term or a "#". My cards on the right in white are me working through the steps. The total spends are not matching up. 

My formula I am using for the simulated weighted average payment term is:

SimulatedWAPT 2 = 
VAR SimVendor = SELECTEDVALUE('SC Data'[Vendor Name])
VAR SimDays = 'New Payment Term'[New Payment Term Value]
VAR OriginalWAPT = DIVIDE(
    SUMX(
        'SC Data',
        'SC Data'[Spend] * RELATED('Payment Terms'[DAYS])),
        SUM('SC Data'[Spend])
)
VAR VendorOriginalWeighted = 
    SUMX(
        FILTER( 'SC Data', 'SC Data'[Vendor Name] = SimVendor),
        'SC Data'[Spend] * RELATED('Payment Terms'[DAYS]))
VAR VendorSimulatedWeighted =
    SUMX(
        FILTER('SC Data', 'SC Data'[Vendor Name] = SimVendor),
        'SC Data'[Spend] * SimDays
    )
VAR AdjustedWeightedSum = 
    CALCULATE(
        SUMX(
            'SC Data',
            'SC Data'[Spend] * RELATED('Payment Terms'[DAYS])
        ),
        ALL('SC Data'),
    DATESBETWEEN(
        'SC Data'[Posting Date],
        MIN('SC Data'[Posting Date]),
        MAX( 'SC Data'[Posting Date])
    ),
    NOT(ISBLANK('SC Data'[PO Payment Term])) && 'SC Data'[PO Payment Term] <> "#"
    ) - [4. VendorOriginalWeighted] + [5. VendorSimulatedWeighted]
RETURN
DIVIDE(
    AdjustedWeightedSum,
    CALCULATE(
    SUM('SC Data'[Spend]),
    ALL('SC Data'),
    DATESBETWEEN(
        'SC Data'[Posting Date],
        MIN('SC Data'[Posting Date]),
        MAX( 'SC Data'[Posting Date])
    ),
    NOT(ISBLANK('SC Data'[PO Payment Term])) && 'SC Data'[PO Payment Term] <> "#"
    )
)

[v-kathullac]

Hi @KB88  ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @KB88  ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @KB88  ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac · Accepted solution]

Hi @KB88 ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Can you try this i have changed the dax which will sync with date slicer

SimulatedWAPT_Final = 
VAR SimVendor = SELECTEDVALUE('SC Data'[Vendor Name])
VAR SimDays = SELECTEDVALUE('New Payment Term'[New Payment Term Value])

VAR VendorSpendTable =
    FILTER(
        ALLSELECTED('SC Data'),
        'SC Data'[Vendor Name] = SimVendor &&
        NOT ISBLANK('SC Data'[PO Payment Term]) &&
        'SC Data'[PO Payment Term] <> "#"
    )

VAR VendorOriginalWeighted = 
    SUMX(
        VendorSpendTable,
        'SC Data'[Spend] * RELATED('Payment Terms'[DAYS])
    )

VAR VendorSimulatedWeighted =
    SUMX(
        VendorSpendTable,
        'SC Data'[Spend] * IF(ISBLANK(SimDays), RELATED('Payment Terms'[DAYS]), SimDays)
    )

VAR AllFilteredData =
    FILTER(
        ALLSELECTED('SC Data'),
        NOT ISBLANK('SC Data'[PO Payment Term]) &&
        'SC Data'[PO Payment Term] <> "#"
    )

VAR OriginalWeightedTotal =
    SUMX(
        AllFilteredData,
        'SC Data'[Spend] * RELATED('Payment Terms'[DAYS])
    )

VAR AdjustedWeightedTotal =
    IF(
        NOT ISBLANK(SimVendor) && NOT ISBLANK(SimDays),
        OriginalWeightedTotal - VendorOriginalWeighted + VendorSimulatedWeighted,
        OriginalWeightedTotal
    )

VAR AdjustedTotalSpend = 
    CALCULATE(
        SUM('SC Data'[Spend]),
        AllFilteredData
    )

RETURN
DIVIDE(AdjustedWeightedTotal, AdjustedTotalSpend)

Regards,

Chaithanya.

[v-kathullac]

Hi @KB88 ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Can try with this formula and let us know if you face any issues.

SimulatedWAPT 2 = 
VAR SimVendor = SELECTEDVALUE('SC Data'[Vendor Name])
VAR SimDays = SELECTEDVALUE('New Payment Term'[New Payment Term Value])  -- What-if value
VAR VendorSpendTable =
    FILTER(
        ALL('SC Data'),
        'SC Data'[Vendor Name] = SimVendor &&
        NOT(ISBLANK('SC Data'[PO Payment Term])) &&
        'SC Data'[PO Payment Term] <> "#"
    )

-- Original weighted total for the vendor
VAR VendorOriginalWeighted = 
    SUMX(
        VendorSpendTable,
        'SC Data'[Spend] * RELATED('Payment Terms'[DAYS])
    )

-- Simulated weighted total for the vendor
VAR VendorSimulatedWeighted =
    SUMX(
        VendorSpendTable,
        'SC Data'[Spend] * SimDays
    )

-- Full dataset (excluding blanks/#) for original weighted term
VAR OriginalWeightedTotal =
    SUMX(
        FILTER(
            ALL('SC Data'),
            NOT(ISBLANK('SC Data'[PO Payment Term])) &&
            'SC Data'[PO Payment Term] <> "#"
        ),
        'SC Data'[Spend] * RELATED('Payment Terms'[DAYS])
    )

-- Adjusted weighted total after vendor term change
VAR AdjustedWeightedTotal = 
    OriginalWeightedTotal - VendorOriginalWeighted + VendorSimulatedWeighted

-- Adjusted total spend (same as base spend)
VAR AdjustedTotalSpend = 
    CALCULATE(
        SUM('SC Data'[Spend]),
        FILTER(
            ALL('SC Data'),
            NOT(ISBLANK('SC Data'[PO Payment Term])) &&
            'SC Data'[PO Payment Term] <> "#"
        )
    )

RETURN
DIVIDE(AdjustedWeightedTotal, AdjustedTotalSpend)

Regards,

Chaithanya.

[KB88]

Thanks Chaithanya!

This measure does not seem to adhere to my date slicer.
Also, when using this measure, now titled SimulatedWAPT3, it brings the weighted average down.

If you do the manual math with my current formula you get 35.176 which matches what my original measure shows for 35.18. If the new payment term is 60 it should be bringing the total weighted average up.