Tableau Function to Power BI

hello Anonymous ,
could you please share the business logic behind your measure ?

Hi Anonymous 

It's been a long long while since I last used Tableu. Try below as a measure

Monthly Savings Estimate =
IF (
    SELECTEDVALUE ( 'DataTable'[Frequency] ) = "Monthly",
    AVERAGEX (
        SUMMARIZE (
            'DataTable',
            'DataTable'[Work Item ID],
            'DataTable'[Savings],
            'DataTable'[Frequency],
            "MonthsSinceClosed", DATEDIFF ( 'DataTable'[Closed Date], TODAY(), MONTH ) * 'DataTable'[Amount]
        ),
        [MonthsSinceClosed]
    ),
    0
)

Hi,

Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

Hi Anonymous 

Please try the below measure:

MonthlyAmountCalc =
IF (
SELECTEDVALUE('Table'[Frequency]) = "Monthly",
AVERAGEX (
SUMMARIZE (
'Table',
'Table'[Work Item ID],
'Table'[Savings],
'Table'[Frequency],
"CalculatedValue", DATEDIFF('Table'[Closed Date], TODAY(), MONTH) * 'Table'[Amount]
),
[CalculatedValue]
),
0
)


 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

Hi Anonymous,

To calculate the average of (months since Closed Date) * Amount specifically for rows where the Frequency is "Monthly" and the Closed Date is not blank, I created a DAX measure that handles this in a structured way. First, it filters the table to only include rows where Frequency is "Monthly" and Closed Date is present.

Then, using VALUES along with ADDCOLUMNS, it iterates over each unique combination of Work Item ID, Savings, and Frequency. For each combination, it calculates the average of DATEDIFF(Closed Date, Today, in months) * Amount.

Finally, it returns the average of these calculated values using AVERAGEX. This helps ensure the result respects both grouping and time-based logic accurately. You can add this measure to a matrix visual, with Frequency and Work Item ID in the rows, and it will return the correct average weighted savings for each item.

Here's the DAX used:

Monthly Estimated Savings =
VAR FilteredTable =
FILTER (
ALL ( 'Table' ),
'Table'[Frequency] = "Monthly" &&
NOT ISBLANK ( 'Table'[Closed Date] )
)
RETURN
AVERAGEX (
ADDCOLUMNS (
VALUES ( 'Table'[Work Item ID] & 'Table'[Savings] & 'Table'[Frequency] ),
"CalcVal",
VAR ItemID = [Work Item ID]
VAR SavingsVal = [Savings]
VAR Freq = [Frequency]
RETURN
AVERAGEX (
FILTER (
FilteredTable,
'Table'[Work Item ID] = ItemID &&
'Table'[Savings] = SavingsVal &&
'Table'[Frequency] = Freq
),
DATEDIFF ( 'Table'[Closed Date], TODAY(), MONTH ) * 'Table'[Amount]
)
),
[CalcVal]
)