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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors