Sales from different table dependant on the promo code

[amadnei]

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)

[amadnei]

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]

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/

[amadnei]

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 🙂

[Anonymous · Accepted solution]

@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