Value of Table1[Column1] multiplied by the Table2[Count of Column2]

[KateThePhoenix]

Ok, let me break down what I'm trying to do I have two tables of data. One is a key, that designates how many products are included in that product code, i.e. People Order that code and receive that number of products

Code	# of products included in code
1101	1
1105	2
1201	1
444	3

Right, now I have another table that includes specific data for each order, including what product code they ordered.

Order number	Product Code ordered
SS-225221	1101
SS-225462	444
SS-225445	1105
SS-515321	444
SS-225455	1101
SS-225897	1201

Now, on my dashboard I have a table visual that shows Count of [Order Number] per Product Code

Code	# of orders for that code
1101	2
1105	1
1201	1
444	2

What I would like is the table above to also have a column that shows how many products total are ordered (remember, some product codes include several products) so it would look something like this:

Code	# of Codes ordered	# of Products ordered
1101	2	2
1105	1	2
1201	1	1
444	2	6

I tried a measure but I think I was messing up because it kept prompting for more and more layers of DAX( )))). I tried a quick measure, but it wanted me to choose Sum, Average, Count, Etc of the # of Products included column, when I really just want to multiply the [# of Codes Ordered] column in the order table by the  [# of products included in code] column from the key table to get the # of products ordered on my dashboard visual.

I'm sure there is some really easy way to to this, but I'm new and felling very dumb right now.

[v-alq-msft]

Hi, @KateThePhoenix 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

Relationship:

You may create two measures as below.

# of orders for that code = 
var tab = 
SUMMARIZE(
    Table1,
    Table1[Code],
    "# of Codes ordered",
    var _code = [Code]
    return
    CALCULATE(
        DISTINCTCOUNT(Table2[Order number]),
        FILTER(
            ALL(Table2),
            Table2[Product Code ordered]=_code
        )
    )
)
return
SUMX(
    tab,
    [# of Codes ordered]
)

# of Products ordered = 
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        Table1,
        Table1[Code],
        Table1[# of products included in code],
        "# of Codes ordered",
        var _code = [Code]
        return
        CALCULATE(
            DISTINCTCOUNT(Table2[Order number]),
            FILTER(
                ALL(Table2),
                Table2[Product Code ordered]=_code
            )
        )
    ),
    "# of Products ordered",
    [# of products included in code]*[# of Codes ordered]
)
return
SUMX(
    tab,
    [# of Products ordered]
)

Result:

Best Regards

Allan

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

[KateThePhoenix]

Well, it's not giving me errors, so that's a start. But, when I copy and paste (changing the Table1/Table2 for the actual tables) it yields a single number over and over again. For every single product code it gives the same number, which appears to be the total for all the codes (example below). I realized I may have set this up for failure. There are other columns in Table2, such as order date and date order was completed. Could these mess this up? Do I need to add those columns as filters? Unfortunately, as I'm dealing with medical information, I can't include my actual data for help.

Code	# of orders for that Code	# of Products ordered
1201	6	11
1101	6	11
1105	6	11
444	6	11

[sturlaws]

Hi @KateThePhoenix 

I have replicated the solution @v-alq-msft provided, and it works fine for me. 

Do you still have issues with this? If so, could you share the report your working with? (upload to onedrive/dropbox/other and share the link)

Cheers,
Sturla

[KateThePhoenix]

Unfortunately, due to my report containing medical information regarding patients, I can't share it here. I'm going to try and re-work this again and see if I can get it to work.

[jdbuchanan71]

@KateThePhoenix 

Assuming you can link the two tables together using the [Code] field this will give you the sum of products per order.

# of Product Ordered = 
SUMX ( Orders, RELATED(Codes[# of products included in code]) )