Summing value from a matrix

[nmatthe2]

Hi created this measure and I am using it in my matrix: 

ReallocateClassesPerDay =

VAR MinT = SELECTEDVALUE(MinThreshold[MinThreshold])

VAR MaxT = SELECTEDVALUE(MaxThreshold[MaxThreshold])

VAR TotalClasses =

    CALCULATE(

        SUM(Sheet1[frequency]),

        ALLEXCEPT(Sheet1, Sheet1[acad_org])

    )

VAR ClassesOnThisDay =

    COALESCE(SUM(Sheet1[frequency]), 0)

VAR PctOnThisDay = DIVIDE(ClassesOnThisDay, TotalClasses)

VAR MinRequired = TotalClasses * MinT

VAR MaxAllowed = TotalClasses * MaxT

VAR RawReallocate =

    SWITCH(

        TRUE(),

        PctOnThisDay < MinT, MinRequired - ClassesOnThisDay,

        PctOnThisDay > MaxT, ClassesOnThisDay - MaxAllowed,

        0

    )

RETURN

    CEILING(RawReallocate, 1)  My matrix has days of the week on the horizontal axis and acad_org on the y axis. I want to sum up how many total classes need to be reallocated from my matrix and visualize this new measure as a card. Whenever I try to create a total, it does not match what is in my matrix

[v-hashadapu]

Hi @nmatthe2 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[ashleyfiore]

You're encountering a common issue in DAX where the total of a measure in a matrix does not match the sum of the visible values, especially when using ALLEXCEPT, SELECTEDVALUE, and SWITCH logic. The root of the issue is that DAX recomputes the measure at the total level with different filter context, which leads to different logic paths being executed.

To properly aggregate your measure, we must recompute it row by row over the same grain as your matrix (acad_org × day) and then sum the results.

Here’s the pattern:

TotalReallocateClasses =
SUMX(
SUMMARIZE(
Sheet1,
Sheet1[acad_org],
Sheet1[day_of_week], -- Adjust if this is not the exact column name
"Realloc", [ReallocateClassesPerDay]
),
[Realloc]
)

[v-hashadapu]

Hi @nmatthe2 , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @nmatthe2 , Thank you for reaching out to the Microsoft Community Forum.

Build a new measure that recreates that row-level context manually using SUMMARIZE, and then sums the results with SUMX. Example:

TotalReallocateClasses =
VAR vTable =
SUMMARIZE(
Sheet1,
Sheet1[acad_org],
Sheet1[day_of_week],
"__Reallocation", [ReallocateClassesPerDay]
)
RETURN
SUMX(vTable, [__Reallocation])

This forces DAX to recalculate [ReallocateClassesPerDay] for every (acad_org, day_of_week) pair exactly as it appears in the matrix and then adds them up to give the true total. After writing this measure, drop it into a card visual to display the correct total number of classes that need to be reallocated.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[jgeddes]

It is likely that the context supplied by your matrix visual is not being supplied to the card visual. Specifically, the day of week and acad_org context would not exist in a card so the VAR TotalClassess and VAR ClassesOnThisDay portion of your measure are not going to be calculated "correctly". You may be able to to fix this by creating a measure that creates a virtual table that summarizes by day and acad_org and then uses your measure as the value. You could then sumx over the table to get your total. 
In pseudo code it would look something like...

Measure = 
var _vTable = 
SUMMARIZE(
    'Table',
    'Table'[acad_org],
    'Table'[DayOfWeek],
    "__value", [YourMeasureName]
)
RETURN
SUMX(
    _vTable,
    [__value]
)

Hope this gets you pointed in the right direction.

[FBergamaschi]

Please include a small rows subset (not an image) of all the tables involved in your request, so that we can import them in Power BI and reproduce the data model. In addition, please include an image of a Power BI visual, showing the issue you are facing, along with the result you are looking for. In this way we can reproduce the problem and help you. Thank you

[Greg_Deckler]

@nmatthe2 First, please vote for this idea: https://community.fabric.microsoft.com/t5/Fabric-Ideas/Matrix-Table-grand-totals-with-Measures/idi-p...

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8