Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
microdri
New Member

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
1 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!

View solution in original post

2 REPLIES 2
Sahir_Maharaj
Super User
Super User

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])

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ About: https://sahirmaharaj.com/about.html
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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!

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors