Optimize Dashboard

There are multiple things wrong with that measure and the ones it depends on.  There are nested iterators, measures inside iterators some using IF( ) which all add up to poor performance.  Can you explain what that measure is supposed to do, so a new measure can be proposed starting from scratch I think?

Regards,

Pat

Can you provide a more detailed explanation of what you need in that calculation?  What aggregation on what columns and what conditions. Maybe walk through one value in the matrix.

Regards,

Pat

Hi,

I have tried to create a new messure and I think I am on the good way, but I am stuck for the moment

This is teh achievement. In september the YTD achievement is lower than 80%. So the sales shouldn't receive any commission that month, but in October his YTD ach is 86%. The sales recuperate as well september.

My Measure

test =
CALCULATE(
SUMX(
VALUES(Dates[Year Month]),
VAR _YTDOTE = TOTALYTD(SUM(OTE[Amount]),Dates[Date],"31/03/2021")*1
VAR _YTDOTE_YTDACH = CALCULATE(TOTALYTD(SUM(OTE[Amount]),Dates[Date],"31/03/2021")*1)*[YTD Ach.]
VAR _YTDAmount = CALCULATE(TOTALYTD(SUM(Actual_CommissionsData_KPICOM[Amount in currency with Current Xrate]),Dates[Date],"31/03/2021") + TOTALYTD(SUM('Manual Transaction'[Amount €]),Dates[Date],"31/03/2021"))
VAR _YTDTarget = TOTALYTD(SUM(Targets[Target]),Dates[Date],"31/03/2021")
VAR _YTDOTE_LM = CALCULATE(TOTALYTD(SUM(OTE[Amount]),Dates[Date],"31/03/2021"),PREVIOUSMONTH(Dates[Date]))
VAR _YTDAch = DIVIDE(_YTDAmount,_YTDTarget)
VAR _YTDOTE_YTDACH_LM = CALCULATE(TOTALYTD(SUM(OTE[Amount]),Dates[Date],"31/03/2021")*[YTD Ach.],PREVIOUSMONTH(Dates[Date]))
RETURN
IF(
_YTDAch<0.8,BLANK(),
IF(
_YTDAch >= 1,
CALCULATE(_YTDOTE -_YTDOTE_YTDACH_LM),
CALCULATE(_YTDOTE_YTDACH -_YTDOTE_YTDACH_LM))
)))

Result

202004 1415.78
202005 1415.78
202006 1415.78
202007 629.13
202008 1033.83
202009
202010 2035.81

In October I should have 2632.51. As the sales has reached YTD 86.20% he recuperate september too. The max reachable is 100%

I do not know how I can add this in the measure. Can someone help me please?

Thanks

Sorry for the delay.  Got busy with work.  Your measure is a better direction; however, I ended up optimizing your original measure.

It turns out it is your [YTD Commission Payout -1M] measure that was causing all your trouble.  One simple fix dropped the refresh time of that visual from >26 sec to <2 sec (on my computer).  It probably still could be optimized further but I figure you'll be happy with this progress.

Here is the new measure

YTD Commission Payout -1Month =
CALCULATE (
    LASTNONBLANKVALUE (
        Dates[Year Month],
        CALCULATE (
            [YTD Commission],
            PREVIOUSMONTH ( Dates[Date] )
        )
    ),
    FILTER (
        ALL ( Dates ),
        Dates[Date]
            <= MAX ( Dates[Date] )
    )
)

I probably wouldn't write this measure this way, but all I did was change from Dates[Date] to Dates[Year Month].  FYI that on the way to figuring that out, I had written your original measure differently.  And with the above measure, the two combined are a little faster.

YTD Commission Payout =
VAR vSummary =
    ADDCOLUMNS (
        VALUES ( Dates[Year Month] ),
        "cYTDComm", [YTD Commission],
        "cYTDCommPay-1M", [YTD Commission Payout -1Month],
        "cManCommPay",
            CALCULATE (
                SUM ( 'Manual Commission Payout'[Amount] )
            )
    )
RETURN
    SUMX (
        vSummary,
        IF (
            ISBLANK ( [cYTDComm] ),
            BLANK (),
            [cYTDComm] - [cYTDCommPay-1M] + [cManCommPay]
        )
    )

It's still over 1.5 sec to refresh that visual, but I didn't have more time to spend on it.

Regards,

Pat