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

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

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.