Automatically Adjust Row A Values to Match Row B Sum with Per-Column Constraints

[microdri]

Hello everyone,
I need a method in Power BI to dynamically adjust values in Row A so that its total matches Row B (139). Key requirements:

• Row A data is in a table updated daily.
• Row B and Row C data are in separate tables with fixed values.
• All three tables are related via a Date column.
• Critical constraint: For each column, Row A’s value must not exceed Row C’s value.

Example Data Structure:

		01/25	02/25	03/25	04/25	05/25	06/25	07/25	08/25	09/25	10/25	11/25	12/25	TT
A	Nb days projet	8	14	17	15	14	10	5	4	17	18	12	12	146
B	Nb days projet cible	10	12	15	13	14	11	9	8	12	13	12	10	139
C	MAX	11	12	17	16	16	12	11	11	13	15	13	13	160

Goal:
Automatically adjust Row A’s values (per column) to ensure:

1. Row A Total = Row B Total (139)
2. Row A ≤ Row C for every column
   
   Any ideas?
   Thanks

[Sahir_Maharaj]

Hello @microdri,

Can you please try this approach:

Adjusted_Nb_Days_Projet = 
VAR SumA = SUM('Table'[Nb days projet])
VAR SumB = SUM('Table'[Nb days projet cible])
VAR AdjustmentFactor = IF(SumA = 0, 1, SumB / SumA)
VAR RawAdjusted = 'Table'[Nb days projet] * AdjustmentFactor
VAR MaxAllowed = 'Table'[MAX]
VAR AdjustedValues = MIN(RawAdjusted, MaxAllowed)

VAR TotalAdjusted = SUMX(ALL('Table'), AdjustedValues)
VAR RemainingGap = SumB - TotalAdjusted
VAR PerColumnCorrection = RemainingGap / COUNTROWS(ALL('Table'))

RETURN 
    MIN(AdjustedValues + PerColumnCorrection, 'Table'[MAX])

[microdri · Accepted solution]

A big thank you for your time and help.
I had to make a small modification to make it work:
_________________________________________________________________

Adjusted_Nb_Days_Projet =

VAR SumA = SUM('Table'[Nb days projet])

VAR SumB = SUM('Table'[Nb days projet cible])

VAR AdjustmentFactor = IF(SumA = 0, 1, SumB / SumA)

VAR RawAdjusted = SUM('Table'[Nb days projet] )* AdjustmentFactor

VAR MaxAllowed = SUM('Table'[MAX])

VAR AdjustedValues = MIN(RawAdjusted, MaxAllowed)

VAR TotalAdjusted = SUMX(FILTER('Table'[Nb days projet]), AdjustedValues)

VAR RemainingGap = SumB - TotalAdjusted

VAR PerColumnCorrection = RemainingGap / COUNTROWS(ALL('Table'))

RETURN

    MIN(AdjustedValues + PerColumnCorrection, SUM('Table'[MAX]))
_________________________________________________________________

I hadn’t considered one thing—that months already passed or in progress are also being modified, and this must be locked by the input.
Is there a possibility for this formula to apply to the remaining months while taking into account the values entered for past and current months? In our example, this would apply from March to December.
If you still have a little time to spare for me, that would be great!