Optimize Dashboard

[Zilliox]

Hi,
I have created several measures, but it is not really optimize. When I publish it, it takes to long before I have the numbers in the Payout visual.

I have done a performance analyze and this is the result. You can see that the DAX query takes much too long

My PBIX file is here.

Can someone help me to optimize the measure "YTD Commission Payout", Please?

Thanks

[mahoneypat · Accepted solution]

Actually, I tried one more thing to optimize one of the dependent measures.  Using this one instead gets it under 400 ms.

YTD Commission Payout -1Month =
VAR vMonths =
    CALCULATETABLE (
        VALUES ( Dates[Year Month] ),
        FILTER (
            ALL ( Dates ),
            Dates[Date]
                < MIN ( Dates[Date] )
        )
    )
VAR vSummary =
    ADDCOLUMNS (
        vMonths,
        "cYTDComm", [YTD Commission]
    )
VAR vMaxMonth =
    MAXX (
        FILTER (
            vSummary,
            [cYTDComm] > 0
        ),
        Dates[Year Month]
    )
RETURN
    SUMX (
        FILTER (
            vSummary,
            Dates[Year Month] = vMaxMonth
        ),
        [cYTDComm]
    )

Regards,

Pat

[Zilliox]

It is working.

Thank you very much for your help

[mahoneypat]

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

[Zilliox]

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

[Zilliox]

Hi @mahoneypat ,

i hope you are fine.

are you able to help me?

thanks

[mahoneypat]

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

[Zilliox]

Hi,

I add an excel file with the formulas in it. I hope I could explain better in the formulas.

https://gofile.io/d/5ng79B 

Really thank you for helping me

[mahoneypat]

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

[Zilliox]

This is really kind of you.

The result is commission payment.

It is based on the YTD achievement. Each person has a different (Query OTE) (this is the yearly commission).

If it YTD achievement is lower than 80% it has to be 0. If higher than 100% than it stays at 100%. The amount has to be in the currency of the person.

The image below is the result I am looking for, but then with a better measure

Thanks again