After joining the Orders and Returns table, Bring order IDs from both the tables to rows. Bring Tears to Filter and select 2012, Bring Order ID from Returns table to filter, and select Null. Bring Sales to text and calculate grand total for columns from Analysis Tab.
Your answer of 72,006 is the result of inner join. In order to get the correct answer you need to create a Left Join between Orders table (this being the left table) and Returns table (this being the right table). In your worksheet all you really need to do is drop Order Date and Status (Status comes from the Returns table) dimensions in the filter shelf; set the year to 2012 and set the status to ‘Null’. Then simply drop your sales measure onto the Text marks card and you’ll get the correct answer of 2,158,725.
What is wrong with this LOD Expression “{ EXCLUDE [Order ID (Returns)]:SUM([Sales])} ” ? I get the answer as 2230731, I am not able to understand what did I miss here.
How to calculate “Find the sale value for items ordered in 2012. Exclude the value of items which were returned.”
My answer is 72,006
Did you do a left join on the Returns worksheet?
After joining the Orders and Returns table, Bring order IDs from both the tables to rows. Bring Tears to Filter and select 2012, Bring Order ID from Returns table to filter, and select Null. Bring Sales to text and calculate grand total for columns from Analysis Tab.
Your answer of 72,006 is the result of inner join. In order to get the correct answer you need to create a Left Join between Orders table (this being the left table) and Returns table (this being the right table). In your worksheet all you really need to do is drop Order Date and Status (Status comes from the Returns table) dimensions in the filter shelf; set the year to 2012 and set the status to ‘Null’. Then simply drop your sales measure onto the Text marks card and you’ll get the correct answer of 2,158,725.
What is wrong with this LOD Expression “{ EXCLUDE [Order ID (Returns)]:SUM([Sales])} ” ? I get the answer as 2230731, I am not able to understand what did I miss here.
Hi
The LOD expression EXCLUDE can only adjust how granular the calculation is, not actually exclude some data points. To do so, you may use filter.
Thanks,
Iris