PROBLEM: Trying to addition on a conditonal column

[Anonymous]

Hey all,

So I have a dashboard which looks at a spend per client.

Now I have mad a conditional column because some clients have an additional charge, and then I do a measure.

Total Charge = SUM('Billing'[Margin]) + SUM('Billing'[Charge])

Now what I have is that all clients total charge comes out as it should, but one clients total is way higher than it should be, and I have found out that it's because that client is appearing three times in the table, and then has three values added against them so instead of their margin been 950 it's 2,850.

Now my question is, is there a way I can do a measure or something to only tell PowerBi to look at the one figure and not add up every margin agaist that client? because it will always be 950

[Aron_Moore]

You have any dummy/sample data to share?

I imagine it looks like this, but could be way off base. Client d is your offender:

Client	Margin	Charge
a	100	700
b	200	150
c	300	100
d	950	200
d	950	300

Do none of the other Clients have repeating rows?

[Anonymous]

And no other client has repeating rows who have a margin. But it could be an issue in future months, so I need some sort of calc that only grabs the one margin no matter how many rows?

[Aron_Moore · Accepted solution]

If it could change, the way I would do it is to break the table into two. One for transactions (charges) and one for characteristics (clients and margin). Then join the two by client or other unique key and viola!

The calculate could be adjusted to account for things like varying margin based on time period etc.

[Anonymous]

Your example is spot on, so when I tell it to add the margin and add the cost onto that is grabs both margins, and I just want the one.

[Aron_Moore]

And there is only the one client that appears more than once? All others are unique?