How can I multiply values from 2 columns =total result A1xB1+A2xB2+etc instead of Total A x Total B

[jeffrey_fuji]

Hi, 

I am creating a report that converts the sales of subcontracted bundles to sales of the components in the bundle. 

I am trying to multiply two columns (column A -> conversion of sales from a bundle to the components within the bundle + column B -> sales amount of the bundles). These columns are from two different tables (table A with the BOM and table B with the subcontracted sales, that are indirectly related (neither has unique values).  

The result should be the sum of the calculated values per row (so A1xB1 + A2xB2 etc.) .

I am trying to do this with a calculated measure that multiplies the values from both columns. The end result is that the total of the new measure is the total sum of column A multiplied by the total sum of column B. 

Could someone please help me to solve this? See screenshots below for the relationships, the measure I have used and the table with the converted sales.

Hopefully someone can help.

Many thanks

[MFelix · Accepted solution]

Hi @jeffrey_fuji,

When you calculate a measure this is based on context so when you say that you multiply column A by colum B on the totals you continue to do that calculation, you need to had a level of complexity on your dax formula in order to change the way it calculates on the subtotals.

In this case the best ways is to use the SUMX formula something like this:

Total Converted sales =
IF (
    HASONEVALUE ( BOM[Bundle number] ),
    [Converted sales],
    SUMX ( ALL ( BOM[Bundle number], BOM[Material Number] ), [Converted sales] )
)

Regards,

MFelix

[v-huizhn-msft]

Hi @jeffrey_fuji,

Have you resolved your issue? Please feel free to ask if you have any other issue.

Best Regards,
Angelia

[Greg_Deckler · Accepted solution]

One way to do this is to create a SUMMARIZE table like this:

EmployeeTable = SUMMARIZE(EmployeeHours,[Week],"Hours",SUM(EmployeeHours[Hours]),"Multi",SUM(EmployeeHours[Column]),"Sum",[SumMeasure])

Obviously for a different use case, but once you have this, you can just add the columns from this summarized table to a Table visualization and you should be good to go. [SumMeasure] is my measure that handles multiplying Hours by Multi.

[MFelix · Accepted solution]

Hi @jeffrey_fuji,

When you calculate a measure this is based on context so when you say that you multiply column A by colum B on the totals you continue to do that calculation, you need to had a level of complexity on your dax formula in order to change the way it calculates on the subtotals.

In this case the best ways is to use the SUMX formula something like this:

Total Converted sales =
IF (
    HASONEVALUE ( BOM[Bundle number] ),
    [Converted sales],
    SUMX ( ALL ( BOM[Bundle number], BOM[Material Number] ), [Converted sales] )
)

Regards,

MFelix

[Greg_Deckler]

Is the issue your measure total row?

See if this post helps:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376