Column total is not showing correct values

[Anonymous]

Dear pbi users,

I am trying to make an easy formula which multiplies the amount * the percentage in a column. See below the formula and screenshot.

NBD (€) = SUM(Grootboekposten[Amount]) * MAX('VA Verdeelsleutels'[NBD])

My problem is that now in the total row (last row) it's not showing the sum of column NBD (€), but the full amount of column amount. See the screen below. I think it has something to do with using sum or sumx.. but can't find the solution.

Can anyone help?

[harshnathani]

Hi @Anonymous ,

Try this measure

Measure = SUMX ( Grootboekposten , Grootboekposten[Amount]) * RELATED( VA Verdeelsleutels'[NBD]))

or

Measure = SUMX ( Grootboekposten , Grootboekposten[Amount]) * VA Verdeelsleutels'[NBD]))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hello,

Thanks for you reply. But it's not working.. The first solution is not working, maybe because there is an other table between the table 'VA Verdeelsleutels' and 'Grootboekposten'.

The second solution also is not working.

You know another solution?

[Anonymous]

You have to use SUMX over a suitable selected field. Something like:

SUMX( VALUES( T[Field] ), [NBD (€)] )

[Anonymous]

Morning,

NBD (€) is the measure i am trying to make. Can you tell me how my dax formula should be?

[Anonymous]

To create a measure that correctly totals you have to figure out what the granularity of the calculation is. Is it some kind of customer name? Is it project name? It would be best if the granularity was a full dimension table. If that was the case you'd write your measure like:

SUMX( Dimension, <expression that calculates the measure for one member of the dimension>)