cancel
Showing results for
Did you mean:
Visitor

## Max of YTD values by category

Hi all,

I am currently working on an insurance dataset that has multiple countries and programs within a country, giving monthly (incremental not ytd) figures.

Data Input:

 Country Program Date Type CURR Value France OD 2018-06-30 2018A EUR 5000.00 France TPL 2018-06-30 2018A EUR 1000.00 France PAR TPL 2018-06-30 2018A EUR 1500.00 France PAR OD 2018-06-30 2018A EUR 2000.00 Germany TPL 2018-06-30 2018A EUR 100.00 France OD 2018-03-31 2018A EUR 5000.00 France PAR TPL 2018-03-31 2018A EUR 1500.00 France PAR OD 2018-03-31 2018A EUR 2000.00 France TPL 2018-03-31 2018A EUR 6000.00 Germany TPL 2018-03-31 2018A EUR 100.00 France OD 2018-09-30 2018A EUR 12000.00 France TPL 2018-09-30 2018A EUR 5000.00 France PAR TPL 2018-09-30 2018A EUR 1500.00 France PAR OD 2018-09-30 2018A EUR 1200.00 Germany TPL 2018-09-30 2018A EUR 100.00

Based on these data I want to create a measure that returns the maximum program ytd value for each country. Also it should be additive, meaning that if I select multiple countries I want to add the maximum ytd values. The result per country should look like the following:

Temp table: ytd values per country & program:

 Reporting Date Country Program Value 2018-03-31 France OD 5000 2018-03-31 France TPL 6000 2018-03-31 France PAR TPL 1500 2018-03-31 France PAR OD 2000 2018-03-31 Germany TPL 100 2018-06-30 France OD 10000 2018-06-30 France TPL 7000 2018-06-30 France PAR TPL 3000 2018-06-30 France PAR OD 4000 2018-06-30 Germany TPL 200 2018-09-30 France OD 22000 2018-09-30 France TPL 7000 2018-09-30 France PAR TPL 4500 2018-09-30 France PAR OD 5200 2018-09-30 Germany TPL 300

Expected output: YTD Values per Country:

 Reporting Date Country Value 2018-03-31 France 6000 2018-03-31 Germany 100 2018-06-30 France 10000 2018-06-30 Germany 200 2018-09-30 France 22000 2018-09-30 Germany 300

Expected output: Overall values per month

 Reporting Date Value 2018-03-31 6100 2018-06-30 10200 2018-09-30 22300

I have found a formula that works fine on a monthly basis, but doesn't give me the YTD values:

MaxYTD = VAR Table1 = SUMMARIZE(Actuals_Monthly,Actuals_Monthly[Country],Actuals_Monthly[Program],"YTD",SUM(Actuals_Monthly[Value],))
RETURN SUMX(GROUPBY(Table1,[Country],"MaxSum",MAXX(CURRENTGROUP(),[YTD])),[MaxSum])

Actual Output:

 Reporting Date Country MaxYTD 2018-03-31 France 6000 2018-03-31 Germany 100 2018-06-30 France 5000 2018-06-30 Germany 100 2018-09-30 France 12000 2018-09-30 Germany 100

However, when I turn to use the totalytd function, the result does not reflect the ytd figures, but again gives me the same results as above.

MaxYTD = VAR Table1 = SUMMARIZE(Actuals_Monthly,Actuals_Monthly[Country],Actuals_Monthly[Program],"YTD",TOTALYTD(Actuals_Monthly[Value],Date))
RETURN SUMX(GROUPBY(Table1,[Country],"MaxSum",MAXX(CURRENTGROUP(),[YTD])),[MaxSum])

I also tried creating a measure for the ytd figure and using that in the summarize formula, again the same result even though the ytd measure itself works perfectly:

ValueYTD:=  TOTALYTD(Actuals_Monthly[Value],Date)

MaxYTD = VAR Table1 = SUMMARIZE(Actuals_Monthly,Actuals_Monthly[Country],Actuals_Monthly[Program],"YTD",ValueYTD)
RETURN SUMX(GROUPBY(Table1,[Country],"MaxSum",MAXX(CURRENTGROUP(),[YTD])),[MaxSum])

I really appreciate any help on this matter, as I have no clue what might be the issue.
Thanks!
2 REPLIES 2
Super User

## Re: Max of YTD values by category

Proud to be a Datanaut!

Community Support Team

## Re: Max of YTD values by category

Hi @imbeck

from you rinformation

"Based on these data I want to create a measure that returns the maximum program ytd value for each country. Also it should be additive, meaning that if I select multiple countries I want to add the maximum ytd values. "

There are three things to do

1.ytd value for each countr

2.maximum program ytd value for each country

3.add the maximum ytd values for multiple countries

since the measure should consider row content influence, these should be in three steps.

I create three measures, if this method doesn't fit your scenario, please let me know.

```ytd per country/program =CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALLEXCEPT ( Table1, Table1[Country], Table1[Program] ), [Date] <= MAX ( [Date] ) ))
YTD Values per Country = MAXX(Table1,[ytd per country/program])

Overall values per month = SUMX(GROUPBY(Table1,[Country]),[YTD Values per Country])```

Best Regards

Maggie