How to display the total only of the values that are presented?

[Anonymous]

I have a report of type table and in it I have a column of value of type decimal number R $, and this value in its origin has some values that are duplicated and in the power bi euconsegui to make a treatment so that these values do not duplicate but if I put this column to be added the total presented will be duplicated, is it possible for me to make the column show the total but in this total only the values presented in the report are added?

[zootsuitryan]

Hi vitexo87,

To clarify, do you need the rows with duplicated amounts removed from the data altogether? Or does the data need to be transformed so that only one of the lines has the total amount on it?

Thanks,

Ryan

[Anonymous]

@zootsuitryan

I need the duplicate values not to be summed, and that only the duplicate value is ignored, and I need only those values presented in the column to be summed in the total value of the column, for example:

Person       value
Person 1    10
Person 1    10
Person 2     5
 Total Sum 15

And the person value 1 can only be displayed once.

[Vvelarde · Accepted solution]

@Anonymous

try with:

Sum-NoDuplicates =
SUMX (
    SUMMARIZE (
        Table1,
        Table1[Person],
        Table1[Value],
        "MAX", MAX ( Table1[Value] )
    ),
    [MAX]
)

[v-huizhn-msft]

Hi @Anonymous,

Based on my understanding, you want to remove duplicate rows then count the total of values, right? If it is, please review the following solutions.

For one thing, in power bi desktop go to query editor (Edit Queries), right click the column and Remove duplicates. You will get the desired single output. Then you can calculate the total sum of values.

For another, if you don't want to change your metadata, you’d better add filter in the formula like the follows.

=SUM(Table[value])-CALCULATE(SUM(Table[value]), Table[value]=”R$ 0” )

If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[kzeidan]

hey, thanks! what is in the red?

=SUM(Table[value])-CALCULATE(SUM(Table[value]), Table[value]=”R$ 0” )

[Anonymous]

@v-huizhn-msft

Unfortunately I can not apply the solution to delete the duplicate values of the column, because if I do I will end up eliminating values from other columns and I can not do this, I tried to use the expression that you informed but is giving error, follows the expression as Remained and the message of error that is giving:

Expression:

Test = SUM (RestoPagar [Unprocessed Registration]) - CALCULATE (SUM (RestoPagar [Unprocessed Registration]); RestoPagar [Unprocessed Registration] = "R $ 0")

log:

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 133, ”.

Thanks for your help