Forum Discussion

eakeekkk's avatar
eakeekkk
Frequent Visitor
4 years ago

Sum child under parent issue

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
SexSum Value
Male18
Total

126

 

Output that I Want

SexSum Value
Male18
Female108
Total126

 

Thank you,

Need Help

5 Replies

  • eakeekkk's avatar
    eakeekkk
    Frequent Visitor
    4 years ago

    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's avatar
      v-jingzhang
      Community Support
      4 years ago

      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.

  • eakeekkk's avatar
    eakeekkk
    Frequent Visitor
    4 years ago

    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's avatar
      v-jingzhang
      Community Support
      4 years ago

      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's avatar
    eakeekkk
    Frequent Visitor
    4 years ago

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