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

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!