Max of YTD values by category

[imbeck]

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!

[v-juanli-msft]

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

[Greg_Deckler]

Can you post sample data and expected output? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490