Forum Discussion

microdri's avatar
microdri
New Member
1 year ago
Solved

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

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
Need Help
  • microdri's avatar
    microdri
    1 year ago

    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!

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    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's avatar
      microdri
      New Member
      1 year ago

      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!