Forum Discussion

SUMESHKUMAR22's avatar
SUMESHKUMAR22
Helper IV
2 years ago
Solved

Matrix Column Grand Total fix

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,  inst...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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]
    )
AlexisOlson's avatar
AlexisOlson
Super User
2 years ago

How is the measure defined?

SUMESHKUMAR22's avatar
SUMESHKUMAR22
Helper IV
2 years ago

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's avatar
    AlexisOlson
    Super User
    2 years ago

    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's avatar
      SUMESHKUMAR22
      Helper IV
      2 years ago

      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's avatar
        AlexisOlson
        Super User
        2 years ago

        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.