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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
GosiaN
New Member

Sum measure based on filters & fixed vs one column

Dear Community,

I have a dataset with following  columns (these are relevant for my problem): 

  • Supplier (text field)
  • Year (report is comparing current year to previous year so there are always two years in the report; currently I have 12 month from 2018 and 10 month from 2019)
  • Month 
  • YTD - yes/no field (I'm calculating which months from previous year should be included in comparison - so it matches months from current year)
  • Country (text field with country name)
  • Region (WE, EE and AP)
  • Ownership (Global, Local, None)
  • SPEND 

For my calculations I need a sum of spend for following filters 2018, YTD and supplier (from slicer).

 

My problem is that when I calculate the spend I'm getting always a split per country which is something that I need to avoid. I need on the other hand to split it per Region and Ownership.

 

How this can be done?

 

I tried following (measures only):

 

SPEND = SUMX(FILTER('DP Summary';'DP Summary'[Payment Year]="2018" && 'DP Summary'[YTD]="yes"

&& 'DP Summary'[Supplier]=[Selected]);'DP Summary'[SPEND])

 

(in measure "Selected" I'm keeping current value from slicer)

I tried to add ALL and ALLSELECTED in the filter function.  ALL shows global number and doesn't split per regions and ownerships. ALLSELECTED gave the same result as above function.

 

I tried SUMMARIZE - function failed to display anything.

I tried CALCULATE with FILTER inside of a FILTER

 

SPEND = CALCULATE('DP Summary'[Spend];FILTER (FILTER (FILTER ('DP Summary';'DP Summary'[Supplier]=[Selected]); 'DP Summary'[YTD] = "yes"); 'DP Summary'[Payment Year] = "2018"))

 

Above gives some result as SUMX... still no idea how to fix it for total vendor not to split per country.

This calculation is just a step in a bigger bridge calculation that I need to make.

 

I'd be extremely grateful for any advice.

 

Perhaps I should use columns instead of Measures...

 

Thanks in advance.

Gosia.

1 REPLY 1
sturlaws
Resident Rockstar
Resident Rockstar

Hi @GosiaN 

 

I hope you have found an answer to your question, but if not, here is an approach on how to solve your problem:

Measure = CALCULATE(SUM('Table'[sale]);FILTER(ALLEXCEPT('Table';'Table'[supplier]);'Table'[year]=2018))

 

A small piece of advice on how to get your questions answered quickly: add sample data and a mockup of your desired output in your posts.

Best,
Sturla

 https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.