Need Help with sum Totals and calculate objetive with Matrix Visual.

[MQUINT-07]

Hello everibody.

wishing you and your family are good and healthy, i want to know if some of you could help me with my necesity.

right now i need to stash some time objetives on customer visits and i want to have this dynamics.

My Code today is:

Objetivos de Visita =

VAR IMT = SELECTEDVALUE(BU[BG Fix])="IQS"

RETURN

VAR MED=SELECTEDVALUE(BU[BG Fix])="MED"

RETURN

VAR MIC = SELECTEDVALUE(BU[BG Fix])="RMS"

RETURN

VAR ObjetivoMes = HASONEFILTER('End_Date'[FiscalYear]) && HASONEFILTER(End_Date[FiscalQuarter]) && HASONEFILTER(End_Date[MonthName])

RETURN

VAR ObjetivoQ = HASONEFILTER('End_Date'[FiscalYear]) && HASONEFILTER(End_Date[FiscalQuarter])

RETURN

VAR ObjetivoFY = HASONEFILTER('End_Date'[FiscalYear])

RETURN

IF(IMT && ObjetivoMes,32,IF(IMT && ObjetivoQ,96, IF(IMT && ObjetivoFY,384,IF(MED && ObjetivoMes,80, IF(MED && ObjetivoQ, 240, IF(MED&&ObjetivoFY, 960,if(MIC&&ObjetivoMes,80,if(MIC&&ObjetivoQ,240, IF(MIC&&ObjetivoFY,960,960)))))))))

and the result is:

but i want to have the actual target respect with the day of the year or day of the month or day of the Q, use fixed just when we need to see the past.

also i want to have the correct total of the sum in the objetiv in Matrix Visual.

i will really apreciate your help and support.

[memepu · Accepted solution]

Hi @MQUINT-07 ,

Please have a try.

Objetivos de Visita =
VAR IMT =
    SELECTEDVALUE ( BU[BG Fix] ) = "IQS"
RETURN
    VAR MED =
        SELECTEDVALUE ( BU[BG Fix] ) = "MED"
    RETURN
        VAR MIC =
            SELECTEDVALUE ( BU[BG Fix] ) = "RMS"
        RETURN
            VAR ObjetivoMes =
                HASONEFILTER ( 'End_Date'[FiscalYear] )
                    && HASONEFILTER ( End_Date[FiscalQuarter] )
                    && HASONEFILTER ( End_Date[MonthName] )
            RETURN
                VAR ObjetivoQ =
                    HASONEFILTER ( 'End_Date'[FiscalYear] )
                        && HASONEFILTER ( End_Date[FiscalQuarter] )
                RETURN
                    VAR ObjetivoFY =
                        HASONEFILTER ( 'End_Date'[FiscalYear] )
                    RETURN
                        IF (
                            IMT && ObjetivoMes,
                            32,
                            IF (
                                IMT && ObjetivoQ,
                                96,
                                IF (
                                    IMT && ObjetivoFY,
                                    384,
                                    IF (
                                        MED && ObjetivoMes,
                                        80,
                                        IF (
                                            MED && ObjetivoQ,
                                            240,
                                            IF (
                                                MED && ObjetivoFY,
                                                960,
                                                IF (
                                                    MIC && ObjetivoMes,
                                                    80,
                                                    IF ( MIC && ObjetivoQ, 240, IF ( MIC && ObjetivoFY, 960, 960 ) )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )

Then create a measure based one the measure.

_contract_ = var _b = SUMMARIZE('end date','end date'[sales Reo],"aaa",'end date'[Objetivos de visita])
return
IF(HASONEVALUE('end date'[sales Reo]),[Objetivos de visita],SUMX(_b,[aaa]))

More details about the error: Dealing with Measure Totals 

If it still does not help, please provide your pbix file without privacy information and desired output.

Best regards.

[Barthel]

Hey,

You can calculate the amount dynamically per period by determining a fraction relative to the entire year. Here too you first determine the selected 'BG'. Then you define the annual targets per 'BG'. Then determine which year target is currently selected. I assume your date table contains dates. (If not, let me know, there are other methods to determine the number of days.) Determine the number of days in the current filter context. Now you can dynamically calculate the target based on the share compared to the annual amount. This is the code:

Objetivos de Visita =
VAR IMT =
    SELECTEDVALUE ( BU[BG Fix] ) = "IQS"
VAR MED =
    SELECTEDVALUE ( BU[BG Fix] ) = "MED"
VAR MIC =
    SELECTEDVALUE ( BU[BG Fix] ) = "RMS"
VAR IMT_Year_Target = 384
VAR MED_Year_Target = 960
VAR MIC_Year_Target = 960
VAR Current_Year_Target =
    SWITCH (
        TRUE,
        IMT, IMT_Year_Target,
        MED, MED_Year_Target,
        MIC, MIC_Year_Target
    )
VAR NumberOfDays =
    COUNT ( End_Date[Date] )
RETURN
    DIVIDE ( Current_Year_Target * NumberOfDays, 365 )

Two other tips: 1. you don't have to write 'RETURN' after every 'VAR' and 2. if you have an extended 'IF' you can change it with 'SWITCH', this is easier to read. Hope this helps.