Forum Discussion
Sales from different table dependant on the promo code
Hi guys,
I am unsure how to proceed DAX wise with my current small project, I'll try to keep it short :
I have a main sales table (t1) with all the sales, with all the generic codes
I have a second table that take thoses sale, with wich I have selected code,
And a final table that take some items that have special code on some occasion.
t1 :
| Item 1 | Code1 | 3 |
| item 1 | Code2 | 5 |
| item 1 | Code3 | 1 |
| Item 2 | Code1 | 8 |
| item 2 | Code2 | 5 |
| item 2 | Code3 | 18 |
t3.
| item1 | Code4 | Sales from t1 |
Things is that the codes from T3 are dependant on the promotion, so item1 might get code4, where as item2 get code5.
Hence why I think making a table (t3) to have the item with the related promo code. The sales are still in t1, but I need to target them item by item for the additionnal code. (hope it make sense )
Any suggestion for a dax measure to get all sales from t1 generic code, + with the promo code ( from t3)
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
5 Replies
Hi, I can't post any work related data but I will make a small fake sheet to explain it better in the coming days if it help other understand.
- VahidDM
Super User
That would be great!
- VahidDM
Hi amadnei
Can you post sample data as text with column names and expected output?
Little bit confused
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar (Add Column names)
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/Here is a mockup of my 2 main table, with the expected result
All sales tableITEM_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_RESULTITEM_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
