Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |