Many to One Relationship Summarizing and Counting

[kcoffman8]

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!

[v-sgandrathi]

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.

[Aburar_123]

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

[kcoffman8]

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 · Accepted solution]

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]

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]

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.