Forum Discussion
Calculate based on the different values
Hi, I need help in calculating the AB Opportunity for a Product and Cat=AB, based on ID, Parent, Year.
Scenario1: The Total Sales for Product 'CARB' is 10,000 mentioned in column ' IDN_Prd_dollars' and the AB sales is 5,000 mentioned in column 'IDN_cat_prd_dollars' now the AB_Opp_Dollar = IDN_Prd_dollars - IDN_cat_prd_dollars, i.e., 10000-5000=5000. here i am calculating AB_Opp dollars only for Cat=AB for the same ID, Parent, Product and Year.
| ID | IDN | Cat | Product | Year | IDN_Prd_dollars | IDN_cat_prd_dollars | AB_Opp_Dollars |
| 1 | CommonSp | AB | CARB | 1 | 10,000 | 5,000 | 5,000 |
| 1 | CommonSp | Big2 | CARB | 1 | 10,000 | 3,000 | 5,000 |
| 1 | CommonSp | D | CARB | 1 | 10,000 | 2,000 | 5,000 |
Scenario2: if a particular Product doesnt have Cat=AB, then AB_Opp_Dollar = 500-0=500
| 1 | CommonSp | Big2 | SOD | 1 | 500 | 500 | 500 |
Scenario3: If Cat=AB and IDN_Prd_dollars, IDN_cat_prd_dollars are same then the AB_Opp_Dollar = 20000 - 20000=0
| 2 | HCA | AB | DAPS | 2 | 20,000 | 20,000 | 0 |
Below is the table and I want the results to be as per in the last column. Kindly help
| ID | IDN | Cat | Product | Year | IDN_Prd_dollars | IDN_cat_prd_dollars | AB_Opp_Dollars |
| 1 | CommonSp | AB | CARB | 1 | 10,000 | 5,000 | 5,000 |
| 1 | CommonSp | Big2 | CARB | 1 | 10,000 | 3,000 | 5,000 |
| 1 | CommonSp | D | CARB | 1 | 10,000 | 2,000 | 5,000 |
| 1 | CommonSp | Big2 | SOD | 1 | 500 | 500 | 500 |
| 2 | HCA | AB | DAPS | 2 | 20,000 | 20,000 | 0 |
| 2 | HCA | D | DAPS | 1 | 5,000 | 5,000 | 5,000 |
| 2 | HCA | AB | FORTE | 2 | 1,000 | 200 | 800 |
| 2 | HCA | D | FORTE | 2 | 1,000 | 700 | 800 |
| 2 | HCA | O | FORTE | 2 | 1,000 | 100 | 800 |
| 2 | CommonSp | AB | IMBR | 1 | 5,000 | 2,000 | 3,000 |
| 2 | CommonSp | O | IMBR | 1 | 5,000 | 3,000 | 3,000 |
| 2 | HCA | AB | SPR | 1 | 2,000 | 2,000 | 0 |
pavanpal1605 , Try a mesure like
calculate(Max(Table[IDN_Prd_dollars]), filter(allselected(Table), Table[Product] = Max(Table[Product]) && Table[Year] = max(Table[Year]) ))
- calculate(Max(Table[IDN_cat_prd_dollars]), filter(allselected(Table), Table[Product] = Max(Table[Product]) && Table[Year] = max(Table[Year]) && Table[Cat] = "AB" ))
2 Replies
pavanpal1605 , Try a mesure like
calculate(Max(Table[IDN_Prd_dollars]), filter(allselected(Table), Table[Product] = Max(Table[Product]) && Table[Year] = max(Table[Year]) ))
- calculate(Max(Table[IDN_cat_prd_dollars]), filter(allselected(Table), Table[Product] = Max(Table[Product]) && Table[Year] = max(Table[Year]) && Table[Cat] = "AB" ))Working!! Thanks a lot 🙂
