Search code examples
powerbidaxmultiplicationpowerbi-desktopmeasure

DAX PowerBi - Multiplication in a measure gives a wrong sum in the total-row of a table-visualization

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?

Solution

  • 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!