Forum Discussion
Sales from different table dependant on the promo code
VahidDM Thanks for your contribution on this thread.
Hi amadnei ,
You can follow the steps below to get it, please find the details in the attachment.
1. Create the relationship between 'All sales' table and 'promotion code' table with the field 'All sales' [[ITEM_NAME]] and 'promotion code' [PROMO_ITEM_NAME]
2. Create a measure as below to get it:
SALE_FROM_CODE = VAR _itemname = SELECTEDVALUE ( 'All sales'[ITEM_NAME] ) RETURN CALCULATE ( SUM ( 'All sales'[SALES] ), FILTER ( 'All sales', 'All sales'[ITEM_NAME] = _itemname && 'All sales'[SALE_CODE] IN VALUES ( 'promotion code'[PROMO_CODE] ) ) )Best Regards
Here is a mockup of my 2 main table, with the expected result
All sales table
| ITEM_ID | ITEM_NAME | SALE_CODE | SALES |
| 1 | BLENDER | CODE1 | 4 |
| 1 | BLENDER | CODE1 | 52 |
| 1 | BLENDER | CODE2 | 5 |
| 1 | BLENDER | CODE3 | 12 |
| 1 | BLENDER | CODE4 | 56 |
| 2 | TABLE | CODE1 | 9 |
| 2 | TABLE | CODE3 | 23 |
| 2 | TABLE | CODE4 | 52 |
| 3 | KNIFE | CODE1 | 12 |
| 3 | KNIFE | CODE2 | 25 |
| 3 | KNIFE | CODE3 | 63 |
| 3 | KNIFE | CODE3 | 12 |
| 3 | KNIFE | CODE4 | 7 |
promotion code
| PROMO_ITEM_ID | PROMO_ITEM_NAME | PROMO_CODE |
| 1 | BLENDER | CODE3 |
| 1 | BLENDER | CODE4 |
| 2 | KNIFE | CODE3 |
EXPECTED_RESULT | |
| ITEM_NAME | SALE_FROM_CODE |
| BLENDER | 68 |
| KNIFE | 75 |
Note that some code from the 2nd table are available on other item that are not in the promo table, but I can't count those in. It's because sometime promo code are used as regular code.
I guess the basic explaination is : IF code + item name are in 1st table, get those sales.
I tried a simple calculate(filter(all(table1) code = code & name = name, but doesn't work properly and take ages to load.
Also tried to use IN function, work very well for few code, but not sure how to add a switch function to change code dependant on the item name.
Hope it's clearer ! thanks 🙂
VahidDM Thanks for your contribution on this thread.
Hi amadnei ,
You can follow the steps below to get it, please find the details in the attachment.
1. Create the relationship between 'All sales' table and 'promotion code' table with the field 'All sales' [[ITEM_NAME]] and 'promotion code' [PROMO_ITEM_NAME]
2. Create a measure as below to get it:
SALE_FROM_CODE =
VAR _itemname =
SELECTEDVALUE ( 'All sales'[ITEM_NAME] )
RETURN
CALCULATE (
SUM ( 'All sales'[SALES] ),
FILTER (
'All sales',
'All sales'[ITEM_NAME] = _itemname
&& 'All sales'[SALE_CODE] IN VALUES ( 'promotion code'[PROMO_CODE] )
)
)
Best Regards