Forum Discussion

Zilliox's avatar
Zilliox
Helper IV
5 years ago
Solved

Optimize Dashboard

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...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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's avatar
Zilliox
Helper IV
5 years ago

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.

Zilliox_0-1606808685982.png


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's avatar
Zilliox
Helper IV
5 years ago

Hi mahoneypat ,

i hope you are fine.

are you able to help me?

 

thanks