Measure used in Matrix doesn't show expected values in Totals

[SahityaYeruband]

Hi All,

I have created a measure in Matrix, which works fine at the Month level but the total isn't populating correctly at the row level.

Eg : 

Country	Dept	Jan 2024	Feb 2024	March 2024	Actual Total	Expected Total
USA	HR			10	<BLANK>	10
USA	Sales		4		4	4

Below is the measure : 

Total Hits Report page =

//Generate YYYYMM from selected Slicers
var _year = SELECTEDVALUE(Usage[Year_Index])
var _mth = SELECTEDVALUE(Usage[Month Name])
var _mth_num = maxx(FILTER((Usage), Usage[Month Name] = _mth), Usage[Month Num])
var _selected_date = 100*_year + _mth_num

//Get the required list of reports
var __Listofreports = CALCULATETABLE(VALUES(Usage[Report path]), Usage[Creation_YYYYMM] < _selected_date)

//Calculate Hits for the specific list of reports
var _hits = CALCULATE(SUM(Usage[Total Hits Monthly]), Usage[Report path] in __Listofreports)

var _hits1 = CALCULATE(SUMX(Usage, Usage[Total Hits Monthly]), Usage[Report path] in __Listofreports)

return
_hits1

Neither _hits nor _hits1 is showing expected values.
What is confusing is, total is showing correct values for some and blanks for other, not sure what is happening.

Thanks in advance for your help.

[BeaBF · Accepted solution]

@SahityaYeruband Hi! Try with:

Total Hits Report page =
VAR _year = SELECTEDVALUE(Usage[Year_Index])
VAR _mth = SELECTEDVALUE(Usage[Month Name])
VAR _mth_num = MAXX(FILTER(Usage, Usage[Month Name] = _mth), Usage[Month Num])
VAR _selected_date = 100 * _year + _mth_num

// Handle totals calculation explicitly
VAR _isTotal = ISINSCOPE(Usage[Month Name]) = FALSE

// Get the required list of reports
VAR __Listofreports =
CALCULATETABLE(
VALUES(Usage[Report path]),
Usage[Creation_YYYYMM] < _selected_date
)

// Calculate Hits for the specific list of reports
VAR _hits =
CALCULATE(
SUM(Usage[Total Hits Monthly]),
Usage[Report path] IN __Listofreports
)

// If in Total row, calculate without relying on Month Name
RETURN
IF(
_isTotal,
CALCULATE(
SUM(Usage[Total Hits Monthly]),
Usage[Creation_YYYYMM] < MAX(Usage[Creation_YYYYMM])
),
_hits
)

BBF

[rohit1991 · Accepted solution]

Hi @SahityaYeruband ,
The issue is likely due to how Power BI handles totals in measures within a Matrix visualization. Suggested Fix

Try modifying your measure to use SUMX with an iterating table to ensure row-level calculations roll up correctly:

Total Hits Report page = 
// Generate YYYYMM from selected Slicers
VAR _year = SELECTEDVALUE(Usage[Year_Index])
VAR _mth = SELECTEDVALUE(Usage[Month Name])
VAR _mth_num = MAXX(FILTER(Usage, Usage[Month Name] = _mth), Usage[Month Num])
VAR _selected_date = 100 * _year + _mth_num

// Get the required list of reports
VAR __Listofreports = 
    CALCULATETABLE(
        VALUES(Usage[Report path]), 
        Usage[Creation_YYYYMM] < _selected_date
    )

// Calculate Hits for the specific list of reports using SUMX for iteration
VAR _hits = 
    SUMX(
        FILTER(Usage, Usage[Report path] IN __Listofreports),
        Usage[Total Hits Monthly]
    )

RETURN _hits

[BeaBF · Accepted solution]

@SahityaYeruband Hi! Try with:

Total Hits Report page =
VAR _year = SELECTEDVALUE(Usage[Year_Index])
VAR _mth = SELECTEDVALUE(Usage[Month Name])
VAR _mth_num = MAXX(FILTER(Usage, Usage[Month Name] = _mth), Usage[Month Num])
VAR _selected_date = 100 * _year + _mth_num

// Handle totals calculation explicitly
VAR _isTotal = ISINSCOPE(Usage[Month Name]) = FALSE

// Get the required list of reports
VAR __Listofreports =
CALCULATETABLE(
VALUES(Usage[Report path]),
Usage[Creation_YYYYMM] < _selected_date
)

// Calculate Hits for the specific list of reports
VAR _hits =
CALCULATE(
SUM(Usage[Total Hits Monthly]),
Usage[Report path] IN __Listofreports
)

// If in Total row, calculate without relying on Month Name
RETURN
IF(
_isTotal,
CALCULATE(
SUM(Usage[Total Hits Monthly]),
Usage[Creation_YYYYMM] < MAX(Usage[Creation_YYYYMM])
),
_hits
)

BBF