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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
Anonymous
Not applicable

Incorrect subtotal

Dear all, 

 

I have created a matrix table however the subtotal is incorrect. Could you please help me to fix it?

kyap1993_0-1645382539464.png

kyap1993_1-1645382567683.png

Other than segment_measure, the rest are columns. Please advise me how to get the correct one. Thanks.

Segment_measure = 
var vtable = SUMMARIZE('Master Data','Master Data'[Market],'Master Data'[Event ID],'Master Data'[Meeting Attendee Name],"vColumn",DISTINCTCOUNT('Master Data'[Meeting ID_Attendee]))
return
if(HASONEVALUE('Master Data'[Meeting Attendee Name]),DISTINCTCOUNT('Master Data'[Meeting ID_Attendee]),sumx(vtable,[vColumn]))
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Anonymous 

If you need to get the distinctcount at any level of your hierarchy, then you can simply use the following measure.
Currently, you are adding the distinctcount for MARKET and EVENT ID levels.

Segment_measure = DISTINCTCOUNT ( 'Master Data'[Meeting ID_Attendee] )


ISINSCOPE will address the single item selection with correct results if you go with your existing DAX and if that is what you need

Segment_measure =
VAR vtable =
    SUMMARIZE (
        'Master Data',
        'Master Data'[Market],
        'Master Data'[Event ID],
        'Master Data'[Meeting Attendee Name],
        "vColumn", DISTINCTCOUNT ( 'Master Data'[Meeting ID_Attendee] )
    )
RETURN
    IF (
        ISINSCOPE ( ( 'Master Data'[Meeting Attendee Name] ),
        DISTINCTCOUNT ( 'Master Data'[Meeting ID_Attendee] ),
        SUMX ( vtable, [vColumn] )
    )





Segment_measure =  DISTINCTCOUNT ( 'Master Data'[Meeting ID_Attendee] )

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@Anonymous 

If you need to get the distinctcount at any level of your hierarchy, then you can simply use the following measure.
Currently, you are adding the distinctcount for MARKET and EVENT ID levels.

Segment_measure = DISTINCTCOUNT ( 'Master Data'[Meeting ID_Attendee] )


ISINSCOPE will address the single item selection with correct results if you go with your existing DAX and if that is what you need

Segment_measure =
VAR vtable =
    SUMMARIZE (
        'Master Data',
        'Master Data'[Market],
        'Master Data'[Event ID],
        'Master Data'[Meeting Attendee Name],
        "vColumn", DISTINCTCOUNT ( 'Master Data'[Meeting ID_Attendee] )
    )
RETURN
    IF (
        ISINSCOPE ( ( 'Master Data'[Meeting Attendee Name] ),
        DISTINCTCOUNT ( 'Master Data'[Meeting ID_Attendee] ),
        SUMX ( vtable, [vColumn] )
    )





Segment_measure =  DISTINCTCOUNT ( 'Master Data'[Meeting ID_Attendee] )

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Greg_Deckler
Community Champion
Community Champion

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907



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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler  Hi Greg, I have already putting in the measure same as what you mentioned in your post. However, i am still getting some errors. 

My measure is in my original post. Could you please help me?

kyap1993_0-1645407373474.png

 

Whitewater100
Solution Sage
Solution Sage

Hi: It's difficult to give a solid answer without seeing your model(or example file) and putting in all info in one table is not how PBI shines but

You can give this a go:

FIX SUMX TOTALS =

Var vTable = ADDCOLUMNS(

                VALUES(

'Master Data'[Market]

),

                "@SegMeas",[Segment_measure]

)

 

Var Result = if(HASONEVALUE(

'Master Data'[Market]

),[Segment_measure],SUMX(vtable,[@SegMeas]))

 

Return

    Result

 

I hope this works for you!

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.