Matrix Column Grand Total fix

[SUMESHKUMAR22]

Hi Community,

Need a urgent help!

In the below highlighted estimated spend horizontally the Grand Total Estimated Hrs is coming average but it should be Sum
(e.g. For account management,  instead of 72.70 it should be 145 (in grand total estimated hrs). 


Thanks in advance!

[SUMESHKUMAR22]

@AlexisOlson , ok! The user is actually drilling down & up at row level

(department-> employee and viceversa) .

So , do that affect the above logic?? Any changes in measure required?

thanks!

[AlexisOlson]

How is the measure defined?

[SUMESHKUMAR22]

@AlexisOlson , there is a option in matrix for column grand total. So its auto created because of that. & Estimated hrs measure is:

Estimated hrs =
VAR _check =
    INT (
        NOT ISINSCOPE ( INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[EMPLOYEE_NAME] )
            && ISINSCOPE ( INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[PROJECT_PHASE] )
    )
VAR _check2 =
    INT (
        NOT ISINSCOPE ( INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[EMPLOYEE_NAME] )
            && ISINSCOPE ( INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[PROJECT_PHASE] )
            && NOT ISINSCOPE ( INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[DEPARTMENT_NAME] )
    )
VAR esthrs =
    SUMX (
        SUMMARIZE (
            INTERNAL__PROJECT_MANAGEMENT__BENCHMARK,
            INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[DEPARTMENT_NAME],
            "AvgEst", AVERAGE ( INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[ESTIMATED_HOURS] )
        ),
        [AvgEst]
    )
RETURN
    IF ( _check = 1, esthrs, esthrs )

[AlexisOlson · Accepted solution]

Well, there's your problem. It's defined as the sum of averages over departments.

If you want it to sum over whatever dimension you're using on columns (phase?), then that needs to be part of the definition. For example,

VAR esthrs =
    SUMX (
        SUMMARIZE (
            INTERNAL__PROJECT_MANAGEMENT__BENCHMARK,
            INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[DEPARTMENT_NAME],
            INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[PROJECT_PHASE], /* <-- New line here */
            "AvgEst", AVERAGE ( INTERNAL__PROJECT_MANAGEMENT__BENCHMARK[ESTIMATED_HOURS] )
        ),
        [AvgEst]
    )

[SUMESHKUMAR22]

@AlexisOlson , Thanks for the info.

I want to get the sum of averages in Grandtotal ( across rows and columns for estimated hrs). So I achieved that for department level (vertically) but at horizontal level its still average. So what modification should be done in my existing measure to get sum of average horizontally?

[AlexisOlson]

As I mentioned, you need to include whatever dimension that is as one of the columns you're averaging over before summing. My example was assuming PROJECT_PHASE was the appropriate dimension.

[SUMESHKUMAR22]

Ok!

lastly, the users can drill down to employee level for each department and viceversa (at row level). So does that need any further modification in measure apart from 'phase'?

[AlexisOlson]

Make sure the measure reflects the intended business logic. If there are other dimensions involved (like employee), you need to decide how you expect to see the total. Should it be an average of the rows/columns or a weighted average?

Ideally, your SUMMARIZE should include the most detailed level(s) you want sum over; then it should add up for all levels in a hierarchy above that detail level. Once you get the measure defined right, it should work in any context.