cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MQUINT-07
Regular Visitor

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

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:
 
MQUINT07_0-1659717021076.pngMQUINT07_1-1659717046744.png

 

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.

 

1 ACCEPTED SOLUTION
memepu
Resolver II
Resolver II

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.

View solution in original post

2 REPLIES 2
memepu
Resolver II
Resolver II

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
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors