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?

  • danextian's avatar
    danextian
    Super User
    10 months ago

    Hi dilmatz0401 

    You forgot to specify which colum to aggregate. Try:

    SUMX( SUMMARIZE (...), [CapacityStacked])

    CapacityStacked should refer to a column inside the virtual table and not the measure. You should be able to select the correct one from the intellisense dropdown.

  • 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])

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    10 months ago

    Hi,

    Remove the last closing bracked and place it before the last comma.