How to fix the incorrect total rolled up to the previous levels in a Matrix Table

[xeniaay_2]

Hi

I'd really appreciate if someone can help me in figuring out the solution to solve this problem about incorrect total rolled up to the previous levels in a Matrix Table.  I have a data table like the following which has column A to C put into Rows of a Matrix Table and column D to F put into the Value field of a Matrix Table.  The Total to the 3rd level i.e. the Project level is correct but the Total rolled up to the 2nd level - Resource and 1st level - CIO are incorrect.  I've used a DAX measure: Average Available = AVERAGEX(ALLEXCEPT(YourTableName, YourTableName[CIO], YourTableName[Resource], YourTableName[Project]), CALCULATE(AVERAGE(YourTableName[Available]))) that works well at the lowest level but it's incorrect at the 2 higher levels.

What should be the correct DAX measure that can resolve the incorrect totals for the Average & Percentage columns?

Thank you!

[bhanu_gautam · Accepted solution]

@xeniaay_2 ,

To calculate the average correctly at each level, you can use the AVERAGEX function along with SUMMARIZE to ensure that the average is calculated based on the correct context.

Average Available =
AVERAGEX(
SUMMARIZE(
YourTableName,
YourTableName[CIO],
YourTableName[Resource],
YourTableName[Project],
"AvgAvailable", CALCULATE(AVERAGE(YourTableName[Available]))
),
[AvgAvailable]
)

For Percentage

Total Available =

SUM(YourTableName[Available])

Percentage Available =

DIVIDE(

    [Total Available],

    CALCULATE(

        [Total Available],

        ALLEXCEPT(YourTableName, YourTableName[CIO], YourTableName[Resource])

    ),

    0

)