Forum Discussion

dilmatz0401's avatar
dilmatz0401
Frequent Visitor
10 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!
dilmatz0401's avatar
dilmatz0401
Frequent Visitor
10 months ago

v-veshwara-msft and Ahmedx Thank you for your help. I apologize for the delay in responding - too many competing priorities. 

I tried making a measure as provided. That is:

but as you can see, I got an error message: "The minimum arguement count for the function is 2."

 

Any suggestions?

Ahmedx's avatar
Ahmedx
Super User
10 months ago

You forgot the second parameter for the SUMX function.
Read about сhere
https://dax.guide/sumx/
SUMX( ..., [CapacityStacked])

XCapTestl =
SUMX(
SUMMARIZE( 'XLIST',
'XLIST'[Division],
’XLIST'[Department],
'XLIST'[Subject],
'XLIST'[SEC_COURSE_NAME],
'XLIST'[SEC_NO],
'XLIST'[XListCourse],
"CapacityStacked",[CapacityStacked]),[CapacityStacked])
------or-----
XCapTestl =
SUMX(
SUMMARIZE( 'XLIST',
'XLIST'[Division],
’XLIST'[Department],
'XLIST'[Subject],
'XLIST'[SEC_COURSE_NAME],
'XLIST'[SEC_NO],
'XLIST'[XListCourse]),[CapacityStacked])