Calculated Measure from two tables

[michaelshparber]

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

[Icey]

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]

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!

[camargos88]

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]

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!

[camargos88]

@michaelshparber ,

I've merged them into sales tables, the file is updated.

Does it work ? If no let me know.