Insurance Triangulations

[Anonymous]

Hello,

I require a DAX formula to split a total amount depending on when the month of the amount.  I have put a table to show this below. For example if I have £100,000 in January I need this split out by 24 months (each amount for the month will be £4,167)
or in December the total is £50,000 I need this amount split by 13 months therefore £3,846 monthly.

January	24
February	23
March	22
April	21
May	20
June	19
July	18
August	17
September	16
October	15
November	14
December	13

In theory I want to replicate the table shown below and need to total the columns together.

[lbendlin]

see attached.  Doesn't look pretty as your visual will be too wide.

[Anonymous]

Thanks @lbendlin  The 100,000 figure was just an example purposes and would be different for each month would it possible to have this figure different for each month?

Thanks

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Anonymous]

Very close to the solution you provided, however I want the amount that needs to be split by month based off column B which is the "GWP" amount for that specific month.

Hope that makes more sense. Thanks

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[yudapon27]

dude I have the same problem.. I think your solution will work for me too. I appreciate all your contribution and guidance to the case. Excellent help. Really..

[Anonymous]

Please find below the table. Hope this can provide assitance. The monthly totals at the bottom row are misaligned. Jan-22 "Monthly Total" is £4,167, Feb-22 Monthly Total is £9,384 and so on.

Thanks

Month	GWP	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
Jan-22	£100,000	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167	£    4,167
Feb-22	£120,000		£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217	£    5,217
Mar-22	£50,000			£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273	£    2,273
Apr-22	£75,000				£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571	£    3,571
May-22	£120,000					£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000	£    6,000
Jun-22	£100,000						£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263	£    5,263
Jul-22	£ 60,000							£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333	£    3,333
Aug-22	£80,000								£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706	£    4,706
Sep-22	£220,000									£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750	£  13,750
Oct-22	£200,000										£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333	£  13,333
Nov-22	£90,000											£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429	£    6,429
Dec-22	£100,000												£    7,692	£    7,692	£    7,692	£    7,692	£    7,692	£    7,692	£    7,692	£    7,692	£    7,692	£    7,692	£    7,692	£    7,692	£    7,692
Monthly Total	£4,167	£    9,384	£  11,657	£  15,228	£  21,228	£  26,491	£  29,825	£  34,531	£  48,281	£  61,614	£  68,042	£  75,735	£  75,735	£  75,735	£  75,735	£  75,735	£  75,735	£  75,735	£  75,735	£  75,735	£  75,735	£  75,735	£  75,735	£75,735

[lbendlin]