Forum Discussion

stephaniebl's avatar
stephaniebl
Icon for Helper I rankHelper I
6 years ago
Solved

Sum data between unrelated tables

Hello, I have two sales data tables, one includes captured sales revenue & the other contains adjustments that are made to the revenue due to returns/late additions/etc.    In order to get true sa...
Need Help
mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

Hi stephaniebl ,

 

avoid the M: N relationship and and split your PLTable into a item table and component table.

 

The measure should look something like this.

Measure = SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment])

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

  • stephaniebl's avatar
    stephaniebl
    Icon for Helper I rankHelper I
    6 years ago

    Hello, yes i tried that but unfortunately it is summing all values. mwegener 

    Each rep should show their own distinct revenue rollup. 

     

    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago

      Hi stephaniebl ,

       

      did you change the m:n relationship?

       

      You may download my PBIX file from here.
      Hope this helps.

       

      • stephaniebl's avatar
        stephaniebl
        Icon for Helper I rankHelper I
        6 years ago

        mwegener Thank you so much! That worked, i was doing a many many relationship between my adj/quota to item list but adding a separate table to define comp component with 1:1 relationship to both worked. 

         

        Can you also help with a formula to obtain % actuals to quota?

        I tried this but it doesnt seem correct : 

        % to Quota=(SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment]))/SUM('Adjust & Quota'[Quota])