Forum Discussion
Incorrect subtotal
Dear all,
I have created a matrix table however the subtotal is incorrect. Could you please help me to fix it?
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]))
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 needSegment_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] )
4 Replies
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 needSegment_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] )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!
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/547907Greg_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?
