Totals not matching in matrix visual

[raju8004212]

PTO/Headcount (Current Yr) =

VAR MAXM = MAX(W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM])

VAR FY = MAX(W_MCAL_PERIOD_D[MCAL_YEAR])

VAR dates = FILTER('W_MCAL_PERIOD_D Duplicate', 'W_MCAL_PERIOD_D Duplicate'[MCAL_JULIAN_PERIOD_NUM] <=MAXM && 'W_MCAL_PERIOD_D Duplicate'[MCAL_YEAR] = FY )

VAR count_weeks = max('W_MCAL_PERIOD_D Duplicate'[MCAL_PERIOD_TOTAL_WEEKS])

VAR calc1 =

    Calculate(WC_GL_GA_BILL_F[Vacation Hours],

    REMOVEFILTERS(W_MCAL_PERIOD_D),

    KEEPFILTERS(dates),

    USERELATIONSHIP(W_MCAL_PERIOD_D[ROW_WID],'W_MCAL_PERIOD_D Duplicate'[ROW_WID])

    )

var calc2 =

    CALCULATE(SUM(WC_GL_GA_BILL_F[TOTAL_HOURS])/40,

    REMOVEFILTERS(W_MCAL_PERIOD_D),

    KEEPFILTERS(dates),

    USERELATIONSHIP(W_MCAL_PERIOD_D[ROW_WID],'W_MCAL_PERIOD_D Duplicate'[ROW_WID]),

    W_MCAL_PERIOD_D[MCAL_CAL_WID] = 1011

    )

Return IFERROR(calc1/calc2,0)*count_weeks

by using above calculation in matrix visual im not able to get correct total after enabling row totals 


can any one help me on this

[freginier]

Hey there!

You can try to modify your DAX formula for the total row calculation by using a conditional check. Power BI's behavior when calculating totals can be different, so you might need to manually adjust the logic when calculating for the total.

For example, you can use something like:

TotalCalc =
IF(
HASONEVALUE(W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM]),
[YourRegularCalculation], -- regular formula for individual values
SUMX(ALL(W_MCAL_PERIOD_D), [YourRegularCalculation]) -- aggregate total calculation
)

This approach ensures that when you're at the row level (single period), it uses your regular calculation, but when you're calculating totals, it sums across all rows using the ALL function.

Hope this helps!

😁😁

‌‌‌‌

‌‌‌‌