Forum Discussion

Kefalin's avatar
Kefalin
Regular Visitor
2 years ago

Incorrect Total in matrix visual

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

1 Reply