CODEWITHSUNDEEP
powerbidaxmultiplicationpowerbi-desktopmeasure
Ekfa
Ekfa

Reputation: 115

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?

Upvotes: 1

Views: 4188

Answers (1)

Ekfa
Ekfa

Reputation: 115

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!

Upvotes: 1

Related Questions