Incorrect Total in matrix visual

Kefalin — Mon, 12 Aug 2024 15:37:31 GMT

Dear Community,

I have issue with this DAX measure - see below.
It is not returning correct average in % for Total in matrix visual.
Each QTR is returning correct %, but Total not. Used INSCOPE / NOT INSCOPE function in order to get results for total as well, however average is not OK. In DAX measure i highligthed by red most important parts which are responsible for average calculation.

If anyone could help i would not jump from the window at least 😄

SA FORECAST GAP % Quarters =

VAR CURRENT_YEAR = YEAR(TODAY())

VAR Budget = CALCULATE([AP $k measure], FILTER(ALL(AOP[Cycle]), AOP[Cycle] = CURRENT_YEAR & "-00"))

VAR Mar = CALCULATE([AP $k measure], FILTER(ALL(AOP[Cycle]), AOP[Cycle] = CURRENT_YEAR & "-03"))

VAR Jun = CALCULATE([AP $k measure], FILTER(ALL(AOP[Cycle]), AOP[Cycle] = CURRENT_YEAR & "-06"))

VAR Sep = CALCULATE([AP $k measure], FILTER(ALL(AOP[Cycle]), AOP[Cycle] = CURRENT_YEAR & "-09"))

VAR Dec = CALCULATE([AP $k measure], FILTER(ALL(AOP[Cycle]), AOP[Cycle] = CURRENT_YEAR & "-12"))

VAR Q1 = (Mar - Budget) / Budget)

VAR Q2 = (Jun - Mar) / Mar)

VAR Q3 = (Sep - Jun) / Jun)

VAR Q4 = (Dec - Sep) / Sep)

VAR ValidQuarters =

FILTER(

{Q1, Q2, Q3, Q4},

NOT(ISBLANK([Value]))

)

VAR QuarterlyAverage =

IF(

COUNTROWS(ValidQuarters) = 0,

BLANK(),

AVERAGEX(ValidQuarters, [Value])

)

VAR TotalAdjustment =

IF(

ISINSCOPE(EXPECTED_ORDER_DATE[DATE].[Quarter]),

BLANK(),

QuarterlyAverage

)

VAR X =

SWITCH(

TRUE(),

ISINSCOPE(EXPECTED_ORDER_DATE[DATE].[Quarter]),

SWITCH(

SELECTEDVALUE(EXPECTED_ORDER_DATE[DATE].[Quarter]),

"Qtr 1", Q1,

"Qtr 2", Q2,

"Qtr 3", Q3,

"Qtr 4", Q4,

BLANK()

TotalAdjustment

)

RETURN X

topic Incorrect Total in matrix visual in DAX Commands and Tips

Incorrect Total in matrix visual

Re: Incorrect Total in matrix visual