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 ,

 

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

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

      mwegener Ok i created a date table , how do i add the YYYYMM Data to the table without it being Many to many relationship to my other tables? I am not sure how to add the YYYYMM to only the first of every month.

       

      Date =
      ADDCOLUMNS (
      CALENDAR (DATE(2017,1,1), DATE(2025,12,31)),
      "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
      "Year", YEAR ( [Date] ),
      "Monthnumber", FORMAT ( [Date], "MM" ),
      "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
      "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
      "MonthNameShort", FORMAT ( [Date], "mmm" ),
      "MonthNameLong", FORMAT ( [Date], "mmmm" ),
      "DayOfWeekNumber", WEEKDAY ( [Date] ),
      "DayOfWeek", FORMAT ( [Date], "dddd" ),
      "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
      "Quarter", "Q" & FORMAT ( [Date], "Q" ),
      "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
      )
      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi stephaniebl 

         

        try this

         

        Date = DATE(LEFT('Adjust & Quota'[YYYYMM],4),RIGHT('Adjust & Quota'[YYYYMM],2),1)