Forum Discussion
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 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.
Hi stephaniebl ,
did you change the m:n relationship?
You may download my PBIX file from here.
Hope this helps.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
16 Replies
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.Hello, yes i tried that but unfortunately it is summing all values. mwegener
Each rep should show their own distinct revenue rollup.
Hi stephaniebl ,
did you change the m:n relationship?
You may download my PBIX file from here.
Hope this helps.
