Sum child under parent issue

[eakeekkk]

Hi Folks,

My requirement is to sum all child value under slicer filter parrent id seperate by other column eg. sex, position. My DAX code is correct when show in card visual but when show in metrix visual with other columns. It show only  column value eg. Male with relate to filter's id. Could you have any suggestion?

DAX 

sum value = 

VAR CurrentEmployeeID = SELECTEDVALUE('Member'[ID])

RETURN

CALCULATE (

sum('Member'[Value]),

ALL( 'Member'[ID] ),

PATHCONTAINS ( 'Member'[Path], CurrentEmployeeID )

)

Output came out

Sex	Sum Value
Male	18
Total	126

Output that I Want

Sex	Sum Value
Male	18
Female	108
Total	126

Thank you,

[eakeekkk]

Now I'm already finish this requirement by create another distinct ID table and use this table as a slicer.

Here is my measure

sum value = 

VAR CurrentEmployeeID = CALCULATE(SELECTEDVALUE('DimID'[ID]),ALLEXCEPT('DimID','DimID'[ID]))

RETURN

CALCULATE (

sum('Member'[Value]),

ALL( 'DimID'[ID] ),

PATHCONTAINS ( 'Member'[Path], CurrentEmployeeID )

)

[eakeekkk]

Sorry to reply late already test this measure but it's return the same result don't have output for female

and in edit interaction regard to selectvalue for slicer and ignore interaction the output return blank 

Could you have other solution?

Thank you,

[v-jingzhang]

Hi @eakeekkk 

Your measure is comparing 'Member'[Path] with the current member's id. Is a member possible to have both Male data and Female data in the data table? Can you provide some fake data to show the table structure and the expected output based on that data?

Regards,

Jing

[eakeekkk]

It seem matrix visual is filtered by filter's id of parrent is there anyway to disable filter in row or column of visual

[v-jingzhang]

Hi @eakeekkk 

Try this measure

sum value =
VAR CurrentEmployeeID =
    SELECTEDVALUE ( 'Member'[ID] )
RETURN
    CALCULATE (
        SUM ( 'Member'[Value] ),
        ALL ( 'Member'[ID], 'Member'[Path] ),
        PATHCONTAINS ( 'Member'[Path], CurrentEmployeeID )
    )

Or if you don't want the slicer to filter the matrix visual, you can select the slicer, go to Format > Edit interactions. Disable the slicer from filtering the matrix visual. Reference: Change how visuals interact in a Power BI report 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.