Simulated Weighted Average Payment Term using What-If Parameter

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

My WAPT formula

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

[Anonymous]

Hi @KB88 ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

[KB88]

Sorry for the delay! I have not found a solution to this. I have broken it down step by step and there seems to be an issue with the filtering in one step of the way. I had used up too much time and had to take a pause on this project. 

[Anonymous]

Hi @KB88 ,
Thanks for the update! The issue likely comes from the vendor slicer directly filtering your main data table. When that happens, the measure ends up calculating the weighted average only for the selected vendor, instead of all vendors. The key fix is to use a separate, disconnected vendor table for your slicer. This way, the slicer doesn’t filter the data, it just tells the measure which vendor’s payment term to override. Please let us know if you found any solution or workaround.

Thank you for your cooperation.Have a great day!

Regards,
Pallavi.

[KB88]

That is the issue! If I remove filtering from the slicer, I get the correct number I am looking for in that step. I have a separate Vendor Name table that I created for this, that is linked to SC Data via Vendor name. I inserted that into the slicer, but am still getting the incorrect numbers. 

[Anonymous · Accepted solution]

Hi @KB88 ,
Thank you again for the update!

Since you have already created a disconnected Vendor table, the key is to ensure you're using that disconnected table in your slicer ,not the one that is linked to SC data.If the slicer is based on a table with a relationship, it will still filter the data and affect the weighted average calculation.

Please double-check that the slicer is pointing to the disconnected Vendor table only. Once that is confirmed, your logic might work correctly across all vendors.

Did you get a chance to review my earlier response? If not, please have a look on it and let us know.

Thank you.

[KB88]

The disconnected worked! One note, I had to make a "simvendor" measure that referenced the disconnected table in an IF statement

[Anonymous]

Hi @KB88 ,
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[Anonymous]

Hi @KB88 ,
Thank you @DataNinja777  for the helpful insights!

Upon my undesranding, I tried to recreate in on my local.Here are the steps to follow:

• Create a parameter using below, this creates a table.
  Data type: whole number Minimum: 0  Maximum: 120   and Increment: 5

• Now, create a measure using below:
  New Vendor PT Value = SELECTEDVALUE('New vender PT'[Parameter], 60
• Create a new disconnected Table for Vendor
  VendorList = DISTINCT('SC Data'[Vendor Name])
  
  
• Then,create a measure in SC data table using below:
  
  SimulatedWeightedAvgPT =

  VAR SimVendor = SELECTEDVALUE('VendorList'[Vendor Name]) -- from slicer

  VAR SimDays = [New Vendor PT Value]

  RETURN

  DIVIDE(

      SUMX(

          'SC Data',

          VAR VendorName = 'SC Data'[Vendor Name]

          VAR OriginalPT = RELATED('Payment Terms'[Days])

          VAR SpendAmt = 'SC Data'[Spend]

          VAR DaysToUse =

              IF(VendorName = SimVendor, SimDays, OriginalPT)

          RETURN SpendAmt * DaysToUse

      ),

      SUM('SC Data'[Spend])

  )
•  Now,plot a card visual for SimulatedWeightedAvgPT .Add slicers for vendor name from vendor list and for New vendor list Parameter.
  
  
• Please refer the screenshot and pbix for your reference.
  

If this solution meets your requirement,consider accepting it as solution.

Thank you.

Regards,
Pallavi.

[DataNinja777]

Hi @KB88 ,

You're on the right track with your Weighted Average Payment Term (WAPT) formula, and now you want to simulate what would happen if a vendor’s payment term changes—say, if Vendor B switches entirely to 60-day terms. To do this using a What-If parameter, you'll need to override the existing payment term logic for just that vendor using the parameter value.

Start by creating a What-If parameter in Power BI. Go to Modeling > New Parameter, name it something like New Vendor PT, and set it up with values between 0 and 120 in increments of 5. This creates a new disconnected table with a column [New Vendor PT] and a measure that looks like this:

New Vendor PT Value = SELECTEDVALUE('New Vendor PT'[New Vendor PT], 60)

Now, we want to use this parameter to simulate the new payment term days—but only for Vendor B. So you’ll modify your original WAPT measure to conditionally use the parameter value when the vendor is Vendor B, and otherwise fall back to the actual related days.

Here's the full DAX measure:

SimulatedWeightedAvgPT =
VAR SimVendor = "Vendor B"
VAR SimDays = [New Vendor PT Value]
RETURN
DIVIDE(
    SUMX(
        'SC Data',
        VAR VendorName = 'SC Data'[Vendor Name]
        VAR OriginalPT = RELATED('Payment Terms'[Days])
        VAR SpendAmt = 'SC Data'[Spend]
        VAR DaysToUse =
            IF(VendorName = SimVendor, SimDays, OriginalPT)
        RETURN DaysToUse * SpendAmt
    ),
    SUM('SC Data'[Spend])
)

Drop the New Vendor PT slicer onto your report page. Now when you adjust the slicer, the measure will dynamically calculate the new weighted average assuming all of Vendor B’s transactions have the chosen term. If you'd like to simulate this for a different vendor, just replace "Vendor B" with the name of the other vendor, or make it dynamic by using a slicer and a SELECTEDVALUE() reference to the vendor name.

Best regards,

[KB88]

This was extremely helpful, thank you!

One thing, this shows an individual vendor. So If I am changing the days for Vendor B, the Sim WAPT will be 60 days, but now I want to put the sim WAPT back into the original formula. So all other vendors still have their original PT and vendor be is set to 60. I am using a slicer to pick different vendors to see how this affects the WAPT depending which vendor we can raise.

Using my Sample data the original WAPT for all is 40.33, but if I changed Vendor B to 60 days, the simulated WAPT would be 48.92!