Sum data between unrelated tables

[stephaniebl]

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 sales revenue, i need to sum these two. They come from two tables that are unrelated. I am unsure how to sum the values from both tables. I get a very large number that is the same across all territories.

Adjustments are broken out by 1. Am territory code 2. Comp component. The captured sales revenue is broken down even further to 3. Code (item). I just want to be able to show total revenue by 1. AM territory code & 2. Comp component so that i can compare YOY & vs Quota.

[mwegener · Accepted solution]

Hi @stephaniebl ,

did you change the m:n relationship?

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

[mwegener]

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]

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

Each rep should show their own distinct revenue rollup. 

[mwegener · Accepted solution]

Hi @stephaniebl ,

did you change the m:n relationship?

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

[stephaniebl]

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

Hi @stephaniebl ,

looks good.

Why doesn't it seem right?

[stephaniebl]

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

[mwegener]

Hi @stephaniebl ,

change your monthTable to a complete date table and write the monthly values on the 1st of every month.

https://docs.microsoft.com/en-us/power-bi/desktop-date-tables

You can then use a quick measure to create the YTD calculation.

https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures

[stephaniebl]

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

Hi @stephaniebl 

try this

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

[stephaniebl]

@mwegener or can i do this? I just added a date table & related my month table to it so it is 1:Many

[mwegener]

Hi @stephaniebl ,

you have to add this column in the fact tables.

 - CIA/HCA Cognos Re...

 - Adjust & Quota

You cannot add the date table in your way because it does not filter the fact table.

[stephaniebl]

@mwegener  Ah okay! Done! Now i have a date column added to both data tables that looks like this :

So now how would i create a % to Quota equation that will give me a view for the full year?

[mwegener · Accepted solution]

Hi @stephaniebl ,

are you using the date table now?

Create a sales measure...

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

... and a YTD quick measure

https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures

[stephaniebl]

@mwegener I am now using the date table instead of month table

But i receive this error when using the quick measure year to date sales & filtering by Date

[stephaniebl]

@mwegener  Nevermind it works now based on my original measure with the date table change. Thank you!

[stephaniebl]

@mwegenerdo I add this to my date table as a new column?