Forum Discussion
Resolver IIErratic behavior with Row Totals
As I can't share the .pbix file here, I will try to explain as detailed as possible what's the scenario and the issue I'm facing.
I use several time intelligence functions to get values by M, Q, YTD and FY. However, the calculation is not that simple. In order to get the M value, my client has a logic where we must do the following:
M = Monthly Value
YTD = Year-To-Date Value
PM = Previous Month
LC = Local Currency
GC = Global Currency (€)
FX = Exchange Rate
M GC = (YTD LC * FX) - (YTD PM LC * FX PM) -> (Year-to-date value current month * exchange rate current month) - (year-to-date value of previous month * exchange rate of previous month)
To calculate that, I had to create some functions, which are:
YTD GC:
YTD GC =
VAR Result = SUMX(SUMMARIZE('Fact Data', 'Currency Exchange Rate'[CurrencyCode], 'Date'[Year], 'Date'[Month Number]),
CALCULATE([YTD LC] * SELECTEDVALUE('Fact Data'[FX Rate]))
)
RETURN Result
YTD PM GC:
YTD PM GC =
VAR MM = MAX('Date'[Month Number])
VAR Result = SUMX(SUMMARIZE('Fact Data', 'Currency Exchange Rate'[CurrencyCode], 'Date'[Year], 'Date'[Month Number]),
CALCULATE([YTD LC] * SELECTEDVALUE('Fact Data'[ExchangeRate_EURY]), PREVIOUSMONTH('Date'[Date]))
)
RETURN IF(MM=1, 0, Result)
Note that there is a catch here: To avoid bringing a wrong number in January (as it would take the YTD of December Previous Year, and I don't want that), I apply a condition that assigns 0 if the selected month is January. That seems to be working perfectly for my row values, as you can see in the image below. However, the Row Total keeps taking into account the value of January (I created a Dummy Function to show the value) when summing up all the rows.
The correct number of the Row Total should be 72,144 instead of 129,857, as the value of January (57,713) was removed from my code.
I can't find a reason why the Row Total keeps summing the value of January, even if in my formula I assigned 0 to that month. How can I make sure that the Row Total will take the RETURN IF(MM=1, 0, Result) into consideration when calculating the Subtotal?
I solved the issue by adding the conditional inside the CALCULATE in the SUMX function:
YTD PM GC:YTD PM GC = VAR YYYY = MAX('Date'[Year]) VAR Result = SUMX(SUMMARIZE('Fact Data', 'Currency Exchange Rate'[CurrencyCode], 'Date'[Year], 'Date'[Month Number]), CALCULATE(IF(SELECTEDVALUE('Date'[Year])<YYYY,0,[YTD LC]) * SELECTEDVALUE('Fact Data'[ExchangeRate_EURY]), PREVIOUSMONTH('Date'[Date])) ) RETURN ResultWith that, every data from previous year will be 0, so I keep only data from current year.
4 Replies
AleI solved the issue by adding the conditional inside the CALCULATE in the SUMX function:
YTD PM GC:YTD PM GC = VAR YYYY = MAX('Date'[Year]) VAR Result = SUMX(SUMMARIZE('Fact Data', 'Currency Exchange Rate'[CurrencyCode], 'Date'[Year], 'Date'[Month Number]), CALCULATE(IF(SELECTEDVALUE('Date'[Year])<YYYY,0,[YTD LC]) * SELECTEDVALUE('Fact Data'[ExchangeRate_EURY]), PREVIOUSMONTH('Date'[Date])) ) RETURN ResultWith that, every data from previous year will be 0, so I keep only data from current year.
- lbendlin
Super User
Column totals are computed across all individual rows. your MAX() function has a different result in the column total, it is 6,not 1.
You need to use iterator functions instead.
- Ale
I'm already using iterator function to calculate the values. But for the total I need this verification just to avoid bringing December of previous year there. I didn't find a way to do that inside the SUMX, as it takes row by row to calculate it.
Would you have any idea what I could do there?
- lbendlin
Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
