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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.