cancel
Showing results for
Did you mean:
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:

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
Resolver II

Hi @MQUINT-07 ,

``````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.

2 REPLIES 2
Resolver II

Hi @MQUINT-07 ,

``````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.

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.

Announcements

#### 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.

#### 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!

#### Business Application LATAM Summit 2023

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

#### 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