Forum Discussion
ABS Calculation from two tables
I have two tables, one with order data and one with sales data.
Both are connected to a masterdata table for product information and a date table for joint dates.
In my report i want to visualize product, order, sales and a calculated absolute (ABS) difference between order and sales.
This is working fine on product level but sums up an incorrect total.
Example:
Order_Table:
| Product | Date | Order_Value |
| Product 1 | 2022-08-01 | 1 250 000 |
| Product 2 | 2022-08-01 | 250 000 |
| Product 1 | 2022-09-01 | 750 000 |
| Product 2 | 2022-09-01 | 200 000 |
Sales_Table:
| Product | Date | Sales_Value |
| Product 1 | 2022-08-01 | 1 000 000 |
| Product 2 | 2022-08-01 | 250 000 |
| Product 1 | 2022-09-01 | 800 000 |
| Product 2 | 2022-09-01 | 300 000 |
Below is the result from my current calculation and as you can see I get an ABS Error of 100 000 but I want it to be 400 000.
I have tried som different SUMX formulas but I havent yet succed to get them work
Report:
| Product | Month | Order | Sales | Error | ABS Error |
| Product 1 | 2022-08 | 1 250 000 | 1 000 000 | 250 000 | 250 000 |
| Product 2 | 2022-08 | 250 000 | 250 000 | 0 | 0 |
| Product 1 | 2022-09 | 750 000 | 800 000 | -50 000 | 50 000 |
| Product 2 | 2022-09 | 200 000 | 300 000 | -100 000 | 100 000 |
| Total | 2 450 000 | 2 200 000 | 100 000 | 100 000 (400 000) |
How should I do to get the ABS Error total that I want? Do i need to join tables?
OscarRissveds
Ah, I see.
Try this instead:SUMX ( CROSSJOIN ( VALUES ( 'Product Master Table'[Product] ), VALUES ( 'Date Table'[Month] ) ), CALCULATE ( ABS(SUM(Order_Table[Order_Value])-SUM(Sales_Table[Sales_Value]))))
4 Replies
- m_alireza
Solution Specialist
OscarRissveds
Ah, I see.
Try this instead:SUMX ( CROSSJOIN ( VALUES ( 'Product Master Table'[Product] ), VALUES ( 'Date Table'[Month] ) ), CALCULATE ( ABS(SUM(Order_Table[Order_Value])-SUM(Sales_Table[Sales_Value]))))Thank you m_alireza!
- m_alireza
Hi OscarRissveds ,
Can you try this measure:ABS Error = SUMX(VALUES('Master Data Table'[Product]), ABS(SUM(Order_Table[Order_Value])-SUM(Sales_Table[Sales_Value]))) Hi m_alireza,
Thank you for your answer!
Do you have any solutions how I can filter the formula above on dates (months)?
When I try it its sums every product on a total date horizon even if I filter the report on specific months.
/Oscar
