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.
