Forum Discussion

KB88's avatar
KB88
Frequent Visitor
1 year ago
Solved

Simulated Weighted Average Payment Term using What-If Parameter

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
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.