Calculate SUM of GP based on multiple criterias

[gianmarco_bazz]

Hello everyone,

I have a table with client name,country, service types, brands, total gp, date hierarchy.

I've another table with contractual conditions on service types, brands and countries in scope for each client. I want to show the GP that comes from the contractual conditions.

I used this formula:

"CALCULATE( SUMX('Finance Analytics', 'Finance Analytics'[Gross Profit_Total]),

FILTER('Finance Analytics',

'Finance Analytics'[EBX Team.Country] IN VALUES('PSA Library'[Countries in Scope (groups)])

&& 'Finance Analytics'[Service Type] IN VALUES('PSA Library'[SERVICE TYPE (groups)]) &&

'Finance Analytics'[Brands] IN VALUES('PSA Library'[BRAND IN SCOPE (groups)]) ) )"

The formula works correctly at client level in a table, but gives me the wrong total. If I want to know how much profit is generated outside the contract for all the clients I get the wrong results.

I'd really appreciate your help here.

Thanks in advance

[v-yanjiang-msft · Accepted solution]

Hi @gianmarco_bazz ，

Your problem is that measure returns wrong total, it's a common issue for measure because of context. Here's a article provide a solution in detail with sample, you can refer to it: Why my measure returns the wrong total? How to fix that? (vahiddm.com).

If you want specific formula about your sample, I need more information about the data model.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.