totals on SUMX - how to keep row context

[Yossarian]

Hi,

I've been through this forum and found some posts that broach the subject but do not quite match my requirements.

So, my problem is simple to explain. I need SUMX to actually sum the values in the column.

The picke resides in the following: the measure without SUMX computes to 0 (as it should). On the other hand, the measure with SUMX declaring VARS computes to a very large number because there are implicit filters in the rows that are not being considered when computing the total. And the measure with SUMX not declaring VARS does not compute correctly row wise because the row context limits inherently its scope.

Let me give an example:

The measure in question is the Monetized Qt Correction Var and it is the product of price and the difference of quantities. As the Qt correction only redistributes the total quantity by each line, the total quantities are exactly the same. I need my measure to sum up to -34.698 (the points are the thousands separators).

Let me paste the 3 measures above and the model (very basic):

How should I go about this?

Thanks!

[davehus]

Hi @Yossarian ,

Try this and see if it works.

SUMX(FIRSTNONBLANK('TBL BUYERPROD'[BUYER],1),[Monetised Qt Correction Var (Sumx Meas vers)])

Hope this helps,

Did I help you today? Please accept my solution and hit the Kudos button.

[Yossarian]

Hi @davehus ,

It seems it is not the solution:

Anyway, why restricting the table (on SUMX) to only the first buyer (if I am reading correctly)?
Thanks!

[davehus · Accepted solution]

Hi @Yossarian , Yes, sorry I didn't spot that one. Have you tried summarize. Sorry now I'm only firing bits out because I don't see your underlying model.

Sumx(Summarize(Buyer, Product, "Measure",[Measure]),[Measure])

[Yossarian]

Hi @davehus ,

Just a quick follow up question if you don't mind.
For me, conceptually, the correct approach would be to SUM the column of the summarized table. Why does SUMX on your solution use the calculated values of the inner measure? Shouldn't it just iterate on the summarized table and calculate the measure again - leading to the same results as before?

Thanks again!

[Yossarian]

Hi @davehus ,

Yes, I have tried summarize but with addcolumns instead. I can't really tell how many variations I have tried to no avail.

However, your proposal works for the total! 👌 It works with 2 of the measures (the SUMX VAR version and the no SUMX version). The underlying model is the one I posted, there isn't really anything else (the buyerprod table is used to slice data basically and is one-to-many to sales table).

If it is not possible to consider a measure displaying correctly both rows and total, I will proceed with the "IF workaround" and mark your reply as the solution.

Lifesaviour post, really!

Thanks.