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