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 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 TermDays
A03030
A06060
A04545
A02525
A03535

 

Vendor Name Spend Payment TermDate
Vendor A $  150,000.00A0301/1/2025
Vendor A $  200,000.00A0301/20/2025
Vendor A $    30,000.00A0302/15/2025
Vendor A $    50,000.00A0302/28/2025
Vendor A $    60,000.00A0303/1/2025
Vendor A $    15,000.00A0303/2/2025
Vendor B $  500,000.00A0302/3/2025
Vendor B $    20,000.00A0601/4/2025
Vendor B $    15,000.00A0453/5/2025
Vendor C $    30,000.00A0253/6/2025
Vendor C $    32,000.00A0353/7/2025
Vendor D $  100,000.00A0603/8/2025
Vendor D $  100,000.00A0603/9/2025
Vendor D $  120,000.00A0601/10/2025
Vendor D $    20,000.00A0602/11/2025
Vendor D $    75,000.00A0603/12/2025
Vendor D $    80,000.00A0601/13/2025
Vendor E $  175,000.00A0453/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] <> "#"
    )
)

 

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.

6 Replies

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

       

       

  • v-kathullac's avatar
    v-kathullac
    Community Support
    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  ,

     

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

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

    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.