Forum Discussion

KB88's avatar
KB88
Frequent Visitor
1 year ago
Solved

Simulated weighted average payment type

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 da...
Need Help
  • v-kathullac's avatar
    v-kathullac
    1 year ago

    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's avatar
v-kathullac
Community Support
1 year ago

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's avatar
KB88
Frequent Visitor
1 year ago

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.