Results on rows is correct, but grand total give more than expected.

[rcspaiva]

Hello,

I'm trying get a result of a simple mutiplication between a measure and a column but It's getting wrong.

Does anyone know what can I try to solve it?

SEQPRODUTO = Column

Novo preço = Column

Quantidade projetada = Measure (I cant transform in a column, because has a measure for calc of elasticity to each product )

Faturamento projetado = Measure (My problematic total)

The funniest part is, when I export to excel, my total give correct.

Thx in advance!!!

[Ashish_Mathur]

Hi,

What calculated field formula have you written to compute the values in the last column?

[rcspaiva]

Hello my friend, thx for u reply.

I'm getting crazy with this problem... >.<

[Ashish_Mathur]

Hi,

I cannot understand your requirement.  Do you simply want to add/subtract.  If that be the case, then why are you using the SUMX function.  Please describe the business question and show your expected result.

[rcspaiva]

Hi,

what I need is find a muplication of each row and a correct grand total of these numbers at the last row.

In first column there is a kind of IDproduct (column).

In the second one, a column which has a price for each IDproduct (column).

At the third, there are a result which comes from measure that bring a quantity of sales projetion.

At the fourth I want see the total of sales expected for each IDproduct, and in last row the grand total corretly.

ID product(comes from column)	Price (comes from a column)	Quantity (comes from a measure)	Total (Measure with problem)
999	10	100	1000	Total of each row
1000	20	200	4000
1001	30	1	30
2001	40	2	80
3002	50	4	200
4004	60	50	3000
			8310
			Grand Total what I'm talking about.

Translated

Sorry about my english... ;D

[TomMartens · Accepted solution]

Hey, 

as far as I can see, the wrong value comes from the fact, that the your measure sums up when there is no product that "contributes" to the current Filter Context.

For this reason try this measure

measure = 
  CALCULATE(
    SUMX(
      VALUES('yourtable'[idproduct])
      ,'yourtable'[price] * [Quantity]
  )

If idproduct is on rows than the table returned by VALUES() just contains one row, on the TOTAL level VALUES() contains all the product available in the current filter context.

On the Total level SUMX() iterates over each product, determines the price on row level and multiplies with the measure [Quantity]

Be aware that there is a subtle difference to your measure, here the multiplication happens inside of SUMX( table, expression), where table is given by VALUES() and expression by column * [measure].

Hope this helps

Tom

[rcspaiva]

Hey @TomMartens, I'm a newb on power bi, so I need understand better some concepts. Your solution simply has worked perfectly, and your explanation came in a good moment.

I already wasted almost two days on this, and in another similar case.

Thank u so much!

[TomMartens]

You're welcome, glad my solution did work for you 😉

Cheers

Tom

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.