Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
  • Fowmy's avatar
    Fowmy
    4 years ago

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

     

Fowmy's avatar
Fowmy
Super User
4 years ago

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