Calculating Values By Contract By Country From Multiple Tables

[donovanm]

Hi, 

I am trying to produce a table that will report the number of contracts that my company has in each country by reference.  This is complicated slightly by the the fact that I am using multiple tables.

Underwriter Ref & RiskCountry are within Table 1 and DeclartionCount are in Table 2.

This is the desired result

Underwriter Ref	RiskCountry	No of Policies
ref1AA	Canada	3
ref1AA	Singapore	1
ref1AA	South Africa	1
ref1AA	Taiwan	1
ref1AA	United Arab Emirates	3
ref1AA	United States	2
ref1AG	United States	9
ref1AK	Canada	3,876
ref1AM	United States	180
ref1AN	United States	499
ref1AQ	United States	1

Total                                                    4,576

But what I am currently returning is

Underwriter Ref	RiskCountry	No of Policies
ref1AA	Canada	4,576
ref1AA	Singapore	4,576
ref1AA	South Africa	4,576
ref1AA	Taiwan	4,576
ref1AA	United Arab Emirates	4,576
ref1AA	United States	4,576

Any assistance would be greatfully appreciated.

[v-lgarikapat]

Hi @donovanm ,

Thanks for reaching out to the Microsoft fabric community forum.

@ronrsnfld @FreemanZ @tharunkumarRTK , 

Thanks for your prompt response

@donovanm , 

Please provide sample data that fully covers your issue or question, in a usable format (not as a screenshot). Do not include sensitive information or anything unrelated to the issue. Also, show the expected outcome based on the sample data you provided.

Best Regards,

Lakshmi Narayana

[v-lgarikapat]

Hi @donovanm ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @donovanm ,

If your question has been answered, kindly mark the appropriate response as the Accepted Solution. This small step goes a long way in helping others with similar issues.

We appreciate your collaboration and support!

Best regards,
LakshmiNarayana

[ronrsnfld]

Please also post your Table 1 and Table 2

[tharunkumarRTK]

You can create a relationship and then add 

create a simle DAX measures for No.ofPolicies 

No of Policies = SUM(Table[No of Policies])

Create a table visual and bring the Underwriter Ref & RiskCountry columns and the above measure into it. 

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[donovanm]

Hi, thank you for your suggestion.  I am now closer but I still have an issue so the measure may need some tweaking.

Company Ref	RiskCountry	4,576
ref1AA	Canada	11
ref1AA	Singapore	11
ref1AA	South Africa	11
ref1AA	Taiwan	11
ref1AA	United Arab Emirates	11
ref1AA	United States	11
ref1AG	United States	9
ref1AK	Canada	3,876
ref1AM	United States	180
ref1AN	United States	499
ref1AQ	United States	1
	Total	4,631

Thank you in advance.

[FreemanZ]

hi @donovanm ,

how are Table1 and Table2 related?

[donovanm]

Hi,

I have a relationship on the company reference.

Rgds,

[mussaenda · Accepted solution]

You can do a relationship by using the two columns country and company reference