Forum Discussion

donovanm's avatar
donovanm
Frequent Visitor
1 year ago
Solved

Calculating Values By Contract By Country From Multiple Tables

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 RefRiskCountryNo of Policies
ref1AACanada                                                            3
ref1AASingapore                                                            1
ref1AASouth Africa                                                            1
ref1AATaiwan                                                            1
ref1AAUnited Arab Emirates                                                            3
ref1AAUnited States                                                            2
ref1AGUnited States                                                            9
ref1AKCanada                                                    3,876
ref1AMUnited States                                                        180
ref1ANUnited States                                                        499
ref1AQUnited States                                                            1

Total                                                    4,576

 

But what I am currently returning is

 

Underwriter RefRiskCountryNo of Policies
ref1AACanada                                                    4,576
ref1AASingapore                                                    4,576
ref1AASouth Africa                                                    4,576
ref1AATaiwan                                                    4,576
ref1AAUnited Arab Emirates                                                    4,576
ref1AAUnited States                                                   4,576

 

Any assistance would be greatfully appreciated.

Need Help
  • mussaenda's avatar
    mussaenda
    1 year ago

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

9 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    1 year ago

    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's avatar
      donovanm
      Frequent Visitor
      1 year ago

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

       

      Company RefRiskCountry                                                    4,576
      ref1AACanada11
      ref1AASingapore11
      ref1AASouth Africa11
      ref1AATaiwan11
      ref1AAUnited Arab Emirates11
      ref1AAUnited States11
      ref1AGUnited States9
      ref1AK Canada                                                     3,876
      ref1AM United States                                                         180
      ref1AN United States                                                         499
      ref1AQ United States                                                             1
         
       Total                                                    4,631

       

      Thank you in advance.

  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    1 year ago

    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's avatar
      v-lgarikapat
      Community Support
      1 year ago

      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's avatar
        v-lgarikapat
        Community Support
        1 year ago

        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