Forum Discussion

michaelshparber's avatar
michaelshparber
Advocate V
6 years ago

Calculated Measure from two tables

I have two tables: Sales and Bundle Details.

In my Sales table - some of the sales are shown as Bundles - without Product.

The other table shows these bundle details, but it doesn't show all sales transactions. It has many-to-one relation with Sales.

I would like to create a matrix that shows Product Group / Product with Sales Amount from both tables.

How can I do this with the current data structure without inserting rows / merging / appending tables?

Attached is .pbix

Thank you!

https://1drv.ms/u/s!AoP_9ampPIT77CI8fI-46iLWufTK?e=LeoaZG

 

Need Help

5 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi michaelshparber ,

     

    Check this file: Download PBIX 

     

    I just appended the bundle table in the sales table and filtererd the bundle rows out. Also I created a new column to indentify the bundles transactions.

     

     

     

    • michaelshparber's avatar
      michaelshparber
      Advocate V
      6 years ago

      Hi Ricardo camargos88 ,

      Thanks, but I stated that I am looking for a solution WITHOUT appending the tables.

      In my real scenario the two tables are quite different so I cannot append them.

      Can you think of another way, please?

      Thanks!

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi michaelshparber ,

     

    Two workarounds:

     

    1. Create a calculated table.

    Table = 
VAR Sales1 =
    FILTER ( Sales, Sales[Bundle Sale Ind] <> "Bundle" )
VAR Sales2 =
    SUMMARIZE (
        Sales1,
        Sales[Transaction#],
        Sales[Product Group],
        Sales[Product],
        Sales[Amount]
    )
VAR Bundle1 =
    SUMMARIZE (
        'Bundle Details',
        'Bundle Details'[Transaction#],
        'Bundle Details'[Product Group],
        'Bundle Details'[Product],
        'Bundle Details'[Amount]
    )
VAR Sales3 =
    UNION ( Sales2, Bundle1 )
RETURN
    Sales3

    Then, you can create a Matrix visual like so:

     

    2. Or you can create a Product table first and then create a measure like so:

    Product = SUMMARIZE ( Sales, Sales[Product Group], Sales[Product] )
    Measure = 
VAR Sales1 =
    FILTER ( Sales, Sales[Bundle Sale Ind] <> "Bundle" )
VAR Sales2 =
    SUMMARIZE (
        Sales1,
        Sales[Transaction#],
        Sales[Product Group],
        Sales[Product],
        Sales[Amount]
    )
VAR Bundle1 =
    SUMMARIZE (
        'Bundle Details',
        'Bundle Details'[Transaction#],
        'Bundle Details'[Product Group],
        'Bundle Details'[Product],
        'Bundle Details'[Amount]
    )
VAR Sales3 =
    UNION ( Sales2, Bundle1 )
RETURN
    SUMX (
        FILTER (
            Sales3,
            [Product Group] = SELECTEDVALUE ( 'Product'[Product Group] )
                && [Product] = SELECTEDVALUE ( 'Product'[Product] )
        ),
        [Amount]
    )

     

    BTW, .pbix file attached.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • michaelshparber's avatar
      michaelshparber
      Advocate V
      6 years ago

      Thanks a lot Icey , I think the second option is the way to go for me.

      Just need to make some adjustments so that the totals for Product Groups are calculated.

       

      camargos88  Thank you as well, it's just as I stated, I needed a solution without appending/merging.

      Appreciate your help!