Forum Discussion

kcoffman8's avatar
kcoffman8
Regular Visitor
1 year ago
Solved

Many to One Relationship Summarizing and Counting

Table 1 and Table 2 have a relationship by account ID, ideally I would like a Matrix as seen in the 3rd table. Thank you!
DAX
powerbi
  • Aburar_123's avatar
    Aburar_123
    1 year ago

    Hi kcoffman8 ,

    you can change that measure to Calculated column as below if in case you want to use that calculated bracket as a Column in Matrix visual.

     

    Calculated Bracket =
    var total_value = SUMX(FILTER('Table A','Table A'[Account Id]=EARLIER('Table A'[Account Id])),'Table A'[Value])
    return SWITCH(TRUE(),total_value<=5000,"5k or Less",total_value>5000 && total_value<=10000,"5k - 10K",total_value>10000 && total_value<=15000,"10k - 15K")
     

     

    FYI... below is the base table structure that i am using,

     

     

     

     

FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper User
1 year ago

Connect yhe two tables via account id

 

Group in columns section the columns

Bracket

Account name

 

Put in values the columns values and customer id and for tge latter set a summarization distinctcount

 

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

kcoffman8's avatar
kcoffman8
Regular Visitor
1 year ago

When you say Group in columns section - is this done in Power Query? As the bracket column is a calcualted column using multiple "if" "and" statements and doesnt appear there.