Unable to sum when grouping measures in a matrix

[Koushikrish]

Hello,

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

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.

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.

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.

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.

[Padycosmos]

This video might help:

https://www.youtube.com/watch?v=6rgAkejrup8

[marcuspaula · Accepted solution]

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] )
)

[Koushikrish]

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

[Koushikrish]

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]

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