Add multiple columns together across tables with one being a measure

[Anonymous]

Hello Community,

I have a data model consisting of stock, invoiced and open orders. All are related to a common date dimension. I want to create a table visual displaying these values next to each other on different levels (customer, resource).

In addition I want the user to be able to filter invoiced and open orders using a Slicer by month and year ( I do not have month and year as a column in the table visual).

I do not want the inventory to be fitlered by month and year. The inventory is the current inventory and it should always display as such. I do have one snapshot of the inventory that is dated current date and linked like that to the date dimension.

So in order for the slicer to not filter the inventory I had to make a measure:

CALCULATE(SUM('LDW VW_INVENTORY_DAILY'[QTY SQM]),ALL('LDW DIM_DATES_GBL'[Month]),ALL('LDW DIM_DATES_GBL'[Year]))

I want another column to simply show:

CALCULATE(SUM('LDW VW_INVENTORY_DAILY'[QTY SQM]),ALL('LDW DIM_DATES_GBL'[Month]),ALL('LDW DIM_DATES_GBL'[Year]))+Open Order + Invoiced Orders

But I do not get it to work. I tried using a variable but it won't let me use the variable in the SUM(SUM(invoived)+SUM(openorder)+SUM(Inventory variable). It complains the variable is not of the right type.

How can I create a measure like this? 

Inventory+Open Orders+Actual YTD SQM =

var inventory_unfiltered=CALCULATE(SUM('LDW VW_INVENTORY_DAILY'[QTY SQM]),ALL('LDW DIM_DATES_GBL'[Month]),ALL('LDW DIM_DATES_GBL'[Year]))

return

SUM(SUM(inventory_unfiltered)+SUM('LDW VW_OPS_BACKLOG_GBL'[Qty])+SUM('LDW VW_INVOICED_YTD'[SQM])+SUM('LDW STG_ENTERED_ORDERS_FOR'[Entered Orders Qty SQM]) )

Error: The SUM function only accepts a column reference as an argument.

[Anonymous]

@amitchandak 

So you say it should work if I do not creete a relation at all to the dim table from the inventory table? I will need to try this.

What worked for me now is:

Inventory+Open Orders+Actual YTD SQM = CALCULATE(SUM('LDW VW_INVENTORY_DAILY'[QTY SQM]),ALL('LDW DIM_DATES_GBL'[Month]),ALL('LDW DIM_DATES_GBL'[Year]))+SUM('LDW VW_INVENTORY_DAILY'[QTY SQM])+SUM('LDW VW_OPS_BACKLOG_GBL'[Qty])+SUM('LDW VW_INVOICED_YTD'[SQM])+SUM('LDW STG_ENTERED_ORDERS_FOR'[Entered Orders Qty SQM])

Thank you for your quick response. It really helps, when you are not feeling completly alone in case of a problem in PBI you can't solve yourself.

[amitchandak · Accepted solution]

@Anonymous , You should join the other two tables and do not join inventory with the date table, You can filter a month from the date table and sum of other two will be month-wise and inventory will be full

You can also get month value using

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))