cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imbeck Visitor
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:

 

CountryProgramDateTypeCURRValue
FranceOD2018-06-302018AEUR5000.00
FranceTPL2018-06-302018AEUR1000.00
FrancePAR TPL2018-06-302018AEUR1500.00
FrancePAR OD2018-06-302018AEUR2000.00
GermanyTPL2018-06-302018AEUR100.00
FranceOD2018-03-312018AEUR5000.00
FrancePAR TPL2018-03-312018AEUR1500.00
FrancePAR OD2018-03-312018AEUR2000.00
FranceTPL2018-03-312018AEUR6000.00
GermanyTPL2018-03-312018AEUR100.00
FranceOD2018-09-302018AEUR12000.00
FranceTPL2018-09-302018AEUR5000.00
FrancePAR TPL2018-09-302018AEUR1500.00
FrancePAR OD2018-09-302018AEUR1200.00
GermanyTPL2018-09-302018AEUR100.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 DateCountryProgramValue
2018-03-31FranceOD5000
2018-03-31FranceTPL6000
2018-03-31FrancePAR TPL1500
2018-03-31FrancePAR OD2000
2018-03-31GermanyTPL100
2018-06-30FranceOD10000
2018-06-30FranceTPL7000
2018-06-30FrancePAR TPL3000
2018-06-30FrancePAR OD4000
2018-06-30GermanyTPL200
2018-09-30FranceOD22000
2018-09-30FranceTPL7000
2018-09-30FrancePAR TPL4500
2018-09-30FrancePAR OD5200
2018-09-30GermanyTPL300


Expected output: YTD Values per Country: 

 

Reporting DateCountryValue
2018-03-31France6000
2018-03-31Germany100
2018-06-30France10000
2018-06-30Germany200
2018-09-30France22000
2018-09-30Germany300

 

Expected output: Overall values per month

 

Reporting DateValue
2018-03-316100
2018-06-3010200
2018-09-3022300

 

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 DateCountryMaxYTD
2018-03-31France6000
2018-03-31Germany100
2018-06-30France5000
2018-06-30Germany100
2018-09-30France12000
2018-09-30Germany100

 

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
Super User

Re: Max of YTD values by category

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
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])

5.png

 

Best Regards

Maggie