power bi relationship

[andrei_iuganu]

Hi all.
I'm totally a beginner ... and I need a lot of help from you.
I have an excel file. With the help of Power BI I would like to get the following thing:
The "startstock" worksheet compares it to the "endstock" worksheet and see what quantity is consumed only for the items that are on both the first worksheet and the second worksheet (I may have articles Which are not found in both worksheets).

The file is here https://www.dropbox.com/s/p4hswvs1ltupszd/ian2016.xls?dl=0

thx in advance.

[v-chuncz-msft]

@andrei_iuganu,

You may use DAX below to add a calculated table.

Table =
ADDCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            startstock,
            "end stock", LOOKUPVALUE ( endstock[stock], endstock[code_item], startstock[code_item] )
        ),
        NOT ( ISBLANK ( [end stock] ) )
    ),
    "quantity consumed", startstock[stock] - [end stock]
)

[andrei_iuganu]

i got this message "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."..when i try your DAX formula

[v-chuncz-msft]

@andrei_iuganu,

Check this tutorial.

[andrei_iuganu]

i follow the steps in tutorial and the result is this error "A table of multiple values was supplied where a single value was expected."

[v-chuncz-msft]

@andrei_iuganu,

Check if the following DAX works.

Table =
ADDCOLUMNS (
    FILTER (
        SUMMARIZE (
            startstock,
            startstock[code_item],
            "start stock", SUM ( startstock[stock] ),
            "end stock", SUMX (
                FILTER ( endstock, endstock[code_item] = startstock[code_item] ),
                endstock[stock]
            )
        ),
        NOT ( ISBLANK ( [end stock] ) )
    ),
    "quantity consumed", [start stock] - [end stock]
)

[andrei_iuganu]

i have made some adjustments to your dax formula

Table = ADDCOLUMNS ( FILTER ( SUMMARIZE ( startstock, startstock[Start code_item], "start stock", SUM ( startstock[Start stock] ), "end stock", SUMX ( FILTER ( endstock, endstock[End code_item] = startstock[Start code_item] ), endstock[End stock] ) ), NOT ( ISBLANK ( [end stock] ) ) ), "quantity consumed", [start stock] - [end stock] ) and it work.

but how to summarize depends on month for each product?....and next code item to show name product?

[v-chuncz-msft]

@andrei_iuganu,

Learn more DAX basics before moving forward. To get an exact solution, share us a more complete example.

[andrei_iuganu]

v-chuncz-msft

Okay. It's normal to study ... what I'm going to do next.
But coming back to my situation ... what I want to get is like that:
  We have the product A, which has a code_item to call it "a1", a name - "product a" and a stock at the beginning and a end stock of the month.
This product A appears in a month several times with different values.
Appears in month 1 through month 12.
I want with the help of power bi in month 1 for product A to make the calculation at all stock values at the beginning of the month and at the stock end of the month.
In the results table I will only display once the information: "product a", code_item "a1", stock started month (gather all the values found in the month and display their total), end month stock (add all the values found in that month and display their total), product name "product a", month.
The formula written by you gives me 95% of the result, because the results do not tell my product name nor the month.

[vanessafvg]

@andrei_iuganu what is your primary key?  is it the id_item or code_item? or both together?

[andrei_iuganu]

vanessafvg my primary key is code_item.

[vanessafvg]

how can i email it back to you? @andrei_iuganu

[andrei_iuganu]

Vanessafvg my mail is andrei.iuganu@tectriu.com