Hi there
I hope that you can help me; I have a table with the budget and sales information and I want to create a pivot table that has to show only the products with an Over Sales, that is, products that exceeded the budget but only the amount of the difference between the Sales and the budget, for example, if the budget of a product is 100 USD and the sales were 120 USD, I want the table to only show 20 USD which is the excess of sales. My expresion so far is:
Where:
Sales_Order_Year = Is the dinamic Year Selected
Budget = It is an indicator for those products that do have a sales budget
Source = Source or data, for Sales the ERP System for Budget it is a variable $(Versus) that it is a version selected of the budget
IF((
//SALES
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}>} Domestic_Amount_USD))
/
// BUDGET
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Bgt_Amount_USD))
) >1,
(
//SALES
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}>} Domestic_Amount_USD))
-
// BUDGET
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Bgt_Amount_USD))
)
, 0)
My Table
| PRODUCT | Bgt | Sales | This is the correct ammount | This is what I´m getting on the pivot table |
| Total | 23,587.91 | 24,008.78 | 4,605.66 | 420.87 |
| A | 5770.20 | 6787.64 | 1017.43 | 1017.43 |
| B | 666.47 | 1629.67 | 963.20 | 963.20 |
| C | 447.03 | 1350.32 | 903.29 | 903.29 |
| D | 814.09 | 1196.14 | 382.05 | 382.05 |
| E | 404.16 | 727.70 | 323.54 | 323.54 |
| F | 223.20 | 533.62 | 310.42 | 310.42 |
| G | 229.06 | 400.83 | 171.77 | 171.77 |
| H | 420.75 | 559.62 | 138.87 | 138.87 |
| I | 81.00 | 200.77 | 119.78 | 119.78 |
| J | 248.40 | 322.57 | 74.16 | 74.16 |
| K | 93.97 | 150.09 | 56.12 | 56.12 |
| L | 110.47 | 155.16 | 44.68 | 44.68 |
| M | 160.94 | 189.30 | 28.36 | 28.36 |
| N | 88.65 | 108.95 | 20.29 | 20.29 |
| O | 124.93 | 144.73 | 19.79 | 19.79 |
| P | 101.89 | 110.01 | 8.12 | 8.12 |
| Q | 44.65 | 52.75 | 8.10 | 8.10 |
| R | 39.29 | 47.22 | 7.93 | 7.93 |
| S | 131.07 | 138.82 | 7.75 | 7.75 |
| T | 188.12 | 186.82 | 0.00 | -1.30 |
| U | 20.55 | 13.01 | 0.00 | -7.54 |
| V | 12.00 | 4.20 | 0.00 | -7.80 |
| W | 12.02 | 0.00 | 0.00 | -12.02 |
| X | 14.25 | 0.00 | 0.00 | -14.25 |
| Y | 71.00 | 53.29 | 0.00 | -17.71 |
| Z | 150.48 | 125.85 | 0.00 | -24.63 |
| AA | 41.04 | 12.32 | 0.00 | -28.72 |
| AB | 2829.03 | 2760.87 | 0.00 | -68.16 |
| AC | 128.73 | 40.28 | 0.00 | -88.45 |
| AD | 384.52 | 282.32 | 0.00 | -102.19 |
| AE | 117.73 | 8.57 | 0.00 | -109.16 |
| AF | 318.08 | 205.62 | 0.00 | -112.46 |
| AG | 1061.31 | 900.35 | 0.00 | -160.97 |
| AH | 183.54 | 0.00 | 0.00 | -183.54 |
| AI | 1886.67 | 1693.69 | 0.00 | -192.98 |
| AJ | 439.92 | 193.42 | 0.00 | -246.50 |
| AK | 627.00 | 108.45 | 0.00 | -518.55 |
| AL | 804.60 | 12.87 | 0.00 | -791.73 |
| AM | 4097.10 | 2600.96 | 0.00 | -1496.13 |
The table I want to obtain is:
| PRODUCT | Over Sales |
| Total | 4,605.66 |
| A | 1017.43 |
| B | 963.20 |
| C | 903.29 |
| D | 382.05 |
| E | 323.54 |
| F | 310.42 |
| G | 171.77 |
| H | 138.87 |
| I | 119.78 |
| J | 74.16 |
| K | 56.12 |
| L | 44.68 |
| M | 28.36 |
| N | 20.29 |
| O | 19.79 |
| P | 8.12 |
| Q | 8.10 |
| R | 7.93 |
| S | 7.75 |
Thanks in advanced for your help.
Regards,
Edgar.
