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
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])
    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago

      Hi stephaniebl ,

       

      looks good.

      Why doesn't it seem right?

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

        mwegener when i calculate my % to quota using this formula :

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

         

        It doesnt provide the % to quota vs future dates because i do not have Revenue values for the future. So i am unable to see what the % to Quota is vs the entire year, or this quarter. My data is set up by month so i have a month date table that lists 01-2020 through 12-2025.

         

        How can i calculate my Sales revenue ((SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment])) vs the quota so that i can see how they stand vs the entire year quota? Would it be

         

        Actual Sales =CALCULATE ((SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment])), DATESYTD((ENDOFYEAR('MonthTable'[Column]),122020)

         

        I dont know if this is correct above. Then i would take that

         

        % to Quota = (equation above )/SUM('Adjust & Quota'{Quota])