Forum Discussion

dilmatz0401's avatar
dilmatz0401
Frequent Visitor
11 months ago
Solved

Help with matrix calculations

I need help with calculating totals in a matrix.   I work at a college and we have some crosslisted sections within a course. For example, we may offer one section of Piano I all by itself. We may ...
Need Help
  • dilmatz0401's avatar
    dilmatz0401
    10 months ago

    The two methods suggested in this thread resulted in the same counts as my original (in blue).

    Solution A (green): Include [Capacity Stacked] in SUMMARIZE and added [Capacity Stacked] after SUMMARIZE

    XListTest1 =
        SUMX(
            SUMMARIZE('XLIST',
                'XLIST'[Division],
                'XLIST'[Department],
                'XLIST'[Subject],
                'XLIST'[SEC_COURSE_NAME],
                'XLIST'[SEC_NO],
                'XLIST'[XListCourse],
                "Capacity Stacked", [CapacityStacked]),
                    [Capacity Stacked])
     
    Solution B (orange): Exclude [Capacity Stacked] in SUMMARIZE and added [Capacity Stacked] after SUMMARIZE
    XListTest2 = 
        SUMX(
            SUMMARIZE('XLIST',
                'XLIST'[Division],
                'XLIST'[Department],
                'XLIST'[Subject],
                'XLIST'[SEC_COURSE_NAME],
                'XLIST'[SEC_NO],
                'XLIST'[XListCourse]),
                    [CapacityStacked])
     

    A colleague provided the correct solution, which has two parts. First, group the section numbers.
    Step 1: Create column in table: 

    GroupID = COALESCE ( 'XLIST'[SEC_NO], 'XLIST'[XListCourse] )

    Step 2: Create measure.

    XlistCorrect =
        SUMX (
            VALUES ( 'XLIST'[GroupID] ), 
                CALCULATE (  MAXX ( 'XLIST', 'XLIST'[Cap] )))
     
    THANK YOU for your help! I learned a lot from all of you and I hope the solution is helpful to others!
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
11 months ago

you need to create a new measure

New =
SUMX(
SUMMARIZE(
    'XLIST',
    'XLIST'[Division],
    'XLIST'[Department],
    'XLIST'[Subject],
    'XLIST'[SEC_COURSE_NAME],
    'XLIST'[SEC_NO],
    'XLIST'[XListCourse],
    "CapacityStacked",[CapacityStacked])
)

You should include in the SUMMARIZE function only those columns that are in the rows as in the picture
I don't know what's going on there, so I included everything that's there in the [CapacityStacked] measure

refer to the following
https://biinsight.com/understanding-power-bi-totals-the-math-the-model-and-the-misconceptions/