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 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.
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!

9 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    10 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/

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    10 months ago

    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.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    10 months ago

    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.

  • 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.

  • dilmatz0401's avatar
    dilmatz0401
    Frequent Visitor
    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!