How to dynamically calculate commissions by month

Hi mattwoldt 

Thank you for being part of the Microsoft Fabric Community.

You may consider the following steps to potentially resolve your issue.

• Make sure the 'Data Type' table used in the slicer is not related to your main fact table. It should be a disconnected table strictly used for selection purposes.
• Use the SELECTEDVALUE() function inside measures (not in calculated columns or tables) to capture the user's choice from the slicer.
• Build dynamic measures using conditional logic with SWITCH() or IF() statements that check the selected value and apply the corresponding measure (like Budget, Forecast, Actuals) based on the financial year or any other condition.

Example:

DAX

CopyEdit

DynamicMeasure =

SWITCH(

    TRUE(),

    SELECTEDVALUE('DataType'[Type]) = "Budget" && Year = "FY25", [BudgetMeasure],

    SELECTEDVALUE('DataType'[Type]) = "Forecast" && Year = "FY25", [ForecastMeasure],

    [ActualsMeasure]

)

• Don’t use SELECTEDVALUE() in calculated tables or columns, as those are static and don’t respond to slicer interactions during report use.

This method will allow your visuals to refresh dynamically based on what users select in the slicer, showing the correct figures for the selected type and year.

If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!

Looking forward to your reply!

Best Regards,

Community Support Team _ C Srikanth.

Hello mattwoldt 

Try this 2 measure

MonthlyRevenueAllocated =

VAR CumRev = SELECTEDVALUE('FactTable'[CumulativeRevenue])

VAR PrevCumRev = CumRev - SELECTEDVALUE('FactTable'[MonthlyRevenue])

VAR StartRev = SELECTEDVALUE('TrancheTable'[StartRevenue])

VAR EndRev = SELECTEDVALUE('TrancheTable'[EndRevenue])

VAR RevenueInTranche =

    MIN (

        MAX ( 0, MIN ( CumRev, EndRev ) - MAX ( PrevCumRev, StartRev ) ),

        SELECTEDVALUE('FactTable'[MonthlyRevenue])

    )

RETURN

    RevenueInTranche

MonthlyCommissionByTranche =

[MonthlyRevenueAllocated] * SELECTEDVALUE('TrancheTable'[Rate])

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.