How to display only 1 value in my column in my simple table report and show the other 2 as 0?

[jctorres]

Good afternoon, dear friends.

I'm having a problem applying the sum(TAX_REMAINING) measure in a simple table report. It shows the same value three times, when it should only display one value and the other two values ​​are zero. This is a native error from the database.

Here's an example of how my report is displayed:

So, in the tax code field it shows 3 details and of those it should only show 1 value as shown in the following image:

Therefore, the report already shows me that and I am applying the following measure:

If(not IsNull(aggr(SUBTOTAL_LINEA, NUMERO_TRANSACCION)),SUM(SUBTOTAL_LINEA),0)

But when I select another branch and the transaction number, in my subtotal field it shows me all 0, when it should show me the same different values ​​as they appear in the last column Subtotal MXN, so I don't know how to apply my measure.

Please, I need to know how to apply the formula so I can visualize the data.

I appreciate your support.

I look forward to your comments.

[rubenmarin]

Hi, that looks like an issue with the data model, the TAX_CODE field is not related to the line of the TAX_REMAINING, each value of TAX_CODE has the relation to the same value of TAX_REMAINING, so you will need to check the relations between those fields on the data model.

[henrikalmen]

So you have a knows problem in your database, and in the second image it seems you have done some kind of workaround that is functional for you. How did you achieve that; what is the expression that calculates [Sub Total] in your second image? Did you do it in frontend or in loadscript? 

What is the selection you have made in image 3, where you are not getting the [Sub Total] column as you want it? What are the fields SUBTOTAL_LINEA (is that the backend name for [Sub Total]?) and NUMERO_TRANSACCION used inside the aggr function? And it seems you already have the result you need in [Sub Total MXN]..?