Forum Discussion

amadnei's avatar
amadnei
New Member
2 years ago
Solved

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 s...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
2 years ago

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's avatar
    amadnei
    New Member
    2 years ago

    Here is a mockup of my 2 main table, with the expected result

    All sales table

    ITEM_IDITEM_NAMESALE_CODESALES
    1BLENDERCODE14
    1BLENDERCODE152
    1BLENDERCODE25
    1BLENDERCODE312
    1BLENDERCODE456
    2TABLECODE19
    2TABLECODE323
    2TABLECODE452
    3KNIFECODE112
    3KNIFECODE225
    3KNIFECODE363
    3KNIFECODE312
    3KNIFECODE47

     

    promotion code

    PROMO_ITEM_IDPROMO_ITEM_NAMEPROMO_CODE
    1BLENDERCODE3
    1BLENDERCODE4
    2KNIFECODE3

     

    EXPECTED_RESULT
    		 
    ITEM_NAMESALE_FROM_CODE
    BLENDER68
    KNIFE75

     

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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