Forum Discussion
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!
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,
Aburar_123
Solution Supplier
Solution SupplierHi kcoffman8 ,
you can simply create a relation as below,
and, create the below measure,
Bracket Measure = SWITCH(TRUE(),SUM('Table A'[Value])<=5000,"5k or Less",SUM('Table A'[Value])>5000 && SUM('Table A'[Value])<=10000,"5k - 10K",SUM('Table A'[Value])>10000 && SUM('Table A'[Value])<=15000,"10k - 15K")
Thanks Aburar, this gets me closer, but I cannot use a measure as the top row of a Matrix. How would you suggest I do that, along with a customer count (counting distinct account names, not counting opportunities)?
- Aburar_123
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,
