Forum Discussion
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,
8 Replies
- FBergamaschi
Super User
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
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.
- Aburar_123
Solution Supplier
Hi 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,
v-sgandrathiCommunity Support
Hi kcoffman8,
Thankyou Aburar_123 and FBergamaschi for your reply on the issue.
If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you.
- v-sgandrathi
Hi kcoffman8,
As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.
Thank you.- v-sgandrathi
Hi kcoffman8,
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
Thank you.
