1

I have two tables, "stock" and "prices". In table "stock" I made a calculated column in the DAX-editor (called "Voorraad").

Voorraad = CALCULATE (COUNT(stock[EAN]), stock[Locatie] IN {"magazijn", "ontvangst"})

The two tables are related on "EAN", a many-to-many relationship.

enter image description here

The visualization in PowerBi shows a right outcome for the column "Voorraad". Then I also wanted to add a column in the visualization with "stockvalue", which shows me the multiplication of "Voorraad" by "Inkoop".

enter image description here

This is working out by a measure named "stockvalue" in table "stock":

stockvalue = SUM(stock[Voorraad])* SUM('prices'[inkoop])

Per row this works fine. But putting it in a table-visualization the total row also shows the multiplication of the total-voorraad * the total-inkoop. So this gives (in my example) the output of 379 * € 88,35 = € 33484,65 Which obviously should be € 3133,00

Anyone knowing the right thing to get this working?

Ekfa
  • 93
  • 3
  • 15
  • I changed the measure to a new column in table "stock". Then I used this code to add another column to table "stock": `stockvalue = SUM(stock[Voorraad])* SUM('prices'[inkoop])` So far so good. Unfortunately on the Total-row it also shows the product of: total "voorraad" * total "inkoop" This of course is not what I need to happen. I want the total of "stockvalue" to show up. – Ekfa Dec 22 '20 at 14:22
  • From [Multiply 2 columns by row then get the sum](https://community.powerbi.com/t5/DAX-Commands-and-Tips/Multiply-2-columns-by-row-then-get-the-sum/td-p/801746), applied to your question: `stockvalue = stock[Voorraad]*LOOKUPVALUE(prices[inkoop], stock[EAN], prices[EAN])` – questionto42 Dec 22 '20 at 14:53
  • When I add a column to table "stock" with this DAX-code it doesn't seem to work. An error message shows up saying the column stock[EAN] doesn't excist or doesn't have a relation with a table in the present context. Simultaniously my other calculated column 'voorraad' gets an error sign with an exclamation mark in it. I'm adding this column in the main powerbi-desktop window on the left side, clicking on the table and then adding a column. – Ekfa Dec 22 '20 at 15:22
  • Basicly the `stockvalue = SUM(stock[Voorraad])* SUM('prices'[inkoop])` is working, accept for the total-row. There is also the product shown for total "voorraad" * total "inkoop". – Ekfa Dec 22 '20 at 15:29
  • @Lorenz: could this error appear because of the other calculated column in the same table "stock"? – Ekfa Dec 22 '20 at 15:41
  • Create a measure that is only there to sum the whole column of the stock table where you add the stockvalue: `total_stockvalue = SUM(stock[stockvalue])`. That is taken from [How to add total sum of each rows within group with DAX Power BI](https://stackoverflow.com/questions/59442173/how-to-add-total-sum-of-each-rows-within-group-with-dax-power-bi). Hide the current total of stock table and add that measure instead. Not sure if that helps, I do not have Power BI installed, and it is quite long ago that I have used it. I cannot help with other questions. – questionto42 Dec 22 '20 at 17:24
  • The last comment didn't give me the solution I needed (or at least I didn't get it working. I edited my question, to have it more accurate to the situation I'm in now. – Ekfa Dec 23 '20 at 08:56
  • Yes that is the right workflow. Getting a measure of a measure should not be an issue. I do not know how to go on, hopefully someone else will go on:) Perhaps you better search the internet and try answering yourself, sometimes such questions here are just not answered since they often are duplicates. – questionto42 Dec 23 '20 at 09:19

1 Answers1

1

The solution is found in: Measure Totals, The Final Word

I put in one measure in table "stock"

stockvalue_onerow = SUM(stock[voorraad])*SUM(prices[inkoop])

Then I put in another measure in table "stock"

    Stockvalue = 
VAR __table = SUMMARIZE(stock, [id], "__value",[stockvalue_onerow])
RETURN
IF(HASONEVALUE(stock[id]),[stockvalue_onerow],SUMX(__table,[__value]))

This last one is to be used in the table-visualization! Problem solved!

Ekfa
  • 93
  • 3
  • 15