Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
90 | |
88 | |
70 | |
69 |
User | Count |
---|---|
227 | |
127 | |
118 | |
82 | |
80 |