Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Koushikrish
Helper I
Helper I

Unable to sum when grouping measures in a matrix

Hello,

 

Need some immediate assistance on this issue am facing. So, I have a Category Line Grouping table that looks like this:

Koushikrish_1-1677622541128.png

 

Basically Line Name is the PK and it could belong to any of the different categories. Each Line Name on the Excel is a measure that is then consolidated and used on a matrix. 

I've all the measures for each Line Item ready and if I display each Line Item along with the consolidated summary measure, it works fine and looks like this.

 

Koushikrish_2-1677622597625.png

 

The problem occurs when I group these under their respective Category (I included both Category and Line Name on the columns). This is what it looks then when collapsed. As you can see for Volume and Net Revenue which have only one Line Name, the Summary displays the right number but for VILC/Unit and Total VILC which have 2 different line names the Summary column appears blank. I would expect the sum of VIC & VLC to appear under Total VILC. For VILC/Unit I would expect the sum of VIC/Unit and VLC/Unit.

 

Koushikrish_5-1677622780833.png

 

When I expand the Total VILC and VILC/Unit though I see both the respective Line names. Its just that when I collapse it I don't see the sum showing up on the Category which I what I expect to see.

 

 

Koushikrish_3-1677622733812.png

Is this expected behaviour of Power BI? Or is there something wrong or something else I need to do to fix this? 

 

Would appreciate any help on this. Please let me know if you need more information!

 

Thank you.

 

1 ACCEPTED SOLUTION
marcuspaula
Frequent Visitor

Hey @Koushikrish,

 

At first glance, it looks like your SWITCH measure doesn't contemplate the scenario where it should calculate the top level category.

 

I would suggest adding conditions to contemplate that, like:

 

 

 

VAR LineName = SELECTEDVALUE( 'dimCategory'[Line Name] )
VAR Category = SELECTEDVALUE( 'dimCategory'[Category] )
RETURN
SWITCH(
    TRUE(),

    // Level 2
    LineName = "VOLUME", CALCULATE ( [Volume] ),
    LineName = "NET REVENUE", CALCULATE ( [Net_Revenue] ),
    LineName = "VIC", CALCULATE ( [VIC] ),
    LineName = "VLC", CALCULATE ( [VLC] ),
    LineName = "VIC/Unit", CALCULATE ( [VICPerUnit] ),
    LineName = "VLC/Unit", CALCULATE ( [VLCPerUnit] ),

    // Level 1
    Category = "VOLUME", CALCULATE ( [Volume] ),
    Category = "NET REVENUE", CALCULATE ( [Net_Revenue] ),
    Category = "VILC/Unit", CALCULATE ( [VICPerUnit] + [VLCPerUnit] ),
    Category = "Total VILC", CALCULATE ( [VIC] + [VLC] )
)

 

 

 

View solution in original post

5 REPLIES 5
Padycosmos
Solution Sage
Solution Sage

marcuspaula
Frequent Visitor

Hey @Koushikrish,

 

At first glance, it looks like your SWITCH measure doesn't contemplate the scenario where it should calculate the top level category.

 

I would suggest adding conditions to contemplate that, like:

 

 

 

VAR LineName = SELECTEDVALUE( 'dimCategory'[Line Name] )
VAR Category = SELECTEDVALUE( 'dimCategory'[Category] )
RETURN
SWITCH(
    TRUE(),

    // Level 2
    LineName = "VOLUME", CALCULATE ( [Volume] ),
    LineName = "NET REVENUE", CALCULATE ( [Net_Revenue] ),
    LineName = "VIC", CALCULATE ( [VIC] ),
    LineName = "VLC", CALCULATE ( [VLC] ),
    LineName = "VIC/Unit", CALCULATE ( [VICPerUnit] ),
    LineName = "VLC/Unit", CALCULATE ( [VLCPerUnit] ),

    // Level 1
    Category = "VOLUME", CALCULATE ( [Volume] ),
    Category = "NET REVENUE", CALCULATE ( [Net_Revenue] ),
    Category = "VILC/Unit", CALCULATE ( [VICPerUnit] + [VLCPerUnit] ),
    Category = "Total VILC", CALCULATE ( [VIC] + [VLC] )
)

 

 

 

Thank you @marcuspaula. This helped me fix the issue I was facing!

Koushikrish
Helper I
Helper I

Hello @Greg_Deckler ,

 

This is my measure formula. Can't really post my PBIX here, sorry about that.

 

SWITCH
(
SELECTEDVALUE( 'dimCategory'[Line Name] ),
"VOLUME", CALCULATE ( [Volume] ),
"NET REVENUE", CALCULATE ( [Net_Revenue] ),
"VIC", CALCULATE ( [VIC] ),
"VLC", CALCULATE ( [VLC] ),
"VIC/Unit", CALCULATE ( [VICPerUnit] ),
"VLC/Unit", CALCULATE ( [VLCPerUnit] )
)

 

Thanks for your help.

Greg_Deckler
Super User
Super User

@Koushikrish What is your measure formula? Can you post a link to your PBIX?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.