Help with matrix calculations

[dilmatz0401]

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 also offer another section of Piano I that is crosslisted with other classes. What that means is that there is one instructor for the section, but there may be other sections that meet at the same time in the same room with that instructor. In this case, we have a primary section (Piano I) and then one or more secondary sections (Piano II and Piano III). I want to find out how to add the enrollments correctly between the primary and secondary sections.

Enrollment in Piano I, the primary section, will remain the same but the enrollment in the secondary sections may differ. The goal is to add the secondary sections to the primary section for total enrollment and to to display the information correctly. Here's an example:

Section 64866 is the primary section and it has 7 students enrolled. ENGR-158-64847 is a secondary sections and it has 3 students enrolled. The total number of students in the all of the secondary sections is 8. That number plus the number of students in the primary section equals 15. The capacity of the section is 22, so the fill rate is 68%. The information in the box is correct, but the total capacity, the red circle, is not.

For primary enrollment, I use MAX enrollment of that section, because it doesn't change.

for secondary enrollment, I use SUM of the secondary enrollments.

Total Enrollment =

    CALCULATE(

        MAXX( 'XLIST', 'XLIST'[E1]), ALLEXCEPT( 'XLIST', 'XLIST'[Division], 'XLIST'[Department], 'XLIST'[Subject], 'XLIST'[SEC_COURSE_NAME], 'XLIST'[SEC_NO], 'XLIST'[E1])) +

    CALCULATE(

        SUMX( 'XLIST', 'XLIST'[E2]), ALLEXCEPT( 'XLIST', 'XLIST'[Division], 'XLIST'[Department], 'XLIST'[Subject], 'XLIST'[E1], 'XLIST'[SEC_COURSE_NAME], 'XLIST'[XListCourse], 'XLIST'[SEC_NO]))

What I can't figure out is how to sum the capacities of all of the different sections correctly, the green circles. The current calculation is this:

CapacityStacked =

    CALCULATE(

        MAXX( 'XLIST', 'XLIST'[Cap]),

            ALLEXCEPT( 'XLIST',

                'XLIST'[Division],

                'XLIST'[Department],

                'XLIST'[Subject],

                'XLIST'[SEC_COURSE_NAME],

                'XLIST'[SEC_NO],

                'XLIST'[XListCourse]))

It appears to work at the section level, but not for the entire table.

If you read all the way through, thank you! This is a smart bunch, so I hope you can help me.

[dilmatz0401 · Accepted solution]

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]

@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?

[Ashish_Mathur]

Hi,

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

[Ahmedx]

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

[danextian]

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.

[v-veshwara-msft]

Hi @dilmatz0401 ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-veshwara-msft]

Hi @dilmatz0401 ,
Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out and share the expected output (for example, what the total should be) along with some sample data or a PBIX file without any sensitive information? This will help us assist you more effectively.

Here’s a guide on how to share sample data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[v-veshwara-msft]

Hi @dilmatz0401 ,
Thanks for reaching out to Microsoft Fabric Community.

Thanks @Ahmedx for sharing the measure and detailed explanation along with the reference link.

Using the SUMMARIZE function helps ensure that the CapacityStacked values are grouped and aggregated correctly across the relevant columns in your visual. This approach prevents the total row from incorrectly repeating or skipping capacities when crosslisted sections are involved.

After trying this approach, if the totals still don’t match your expectations, could you please provide the expected output (for example, what the total should be) along with some sample data or a PBIX file without any sensitive information? This will help us assist you more effectively.

Here’s a guide on how to share sample data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[Ahmedx]

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/