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