Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gianmarco_bazz
Frequent Visitor

Calculate SUM of GP based on multiple criterias

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

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-yanjiang-msft
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.