Forum Discussion

maxf94's avatar
maxf94
Frequent Visitor
1 year ago
Solved

Extracting adjustment charge from one table to the other based on multiple parameters

Hello,

 

I do have the following 2 tables that I somehow need to join:

1 - Sales Table

2 - Adjustment Charge table

 

I need to extract the  correct "Adjustment Charge" and apply it to the relevant sale. In order to do that I'll need to check for multiple parameters. 

 

The "Adjustment Charge" table has got multiple values in it depending on the Quantity ordered:

Location  Sublocation  Min Max Charge

Location1 Sublocation2 0   100    10

Location1 Sublocation2 100 200   5

etc.

 

 

I'm looking for something along the lines of:


IF
Location1 = Location2
Sublocation1 = Sublocation2
Date1 <= Date2
Quantity1 <= Quantity2

THEN TotalCost1 + AdjustmentCharge2

 

 

I was hoping to do it via a Calculated Column, however I'm struggling to find a proper relationships for those tables as every one I can think of is a many-many relationship.

 

Any help would be appreciated!

Need Help
  • maxf94's avatar
    maxf94
    1 year ago

    Hello,

     

    I've managed to sort my issue with the following Calculated Column:

     

    Total Cost With Adjustment = 
VAR CurrentLocation = Sales[SalesLocation]
VAR CurrentSublocation = Sales[DeliveryLocation]
VAR CurrentDate = Sales[TransactionDate]
VAR CurrentQuantity = Sales[Quantity]
VAR CurrentTotalCost = Sales[Total Cost]

-- Find the matching adjustment charge
VAR MatchingAdjustment = 
    CALCULATE(
        FIRSTNONBLANK(AdjustmentTable[ChargeValue], 1),
        FILTER(
            ALL(AdjustmentTable),
            AdjustmentTable[SalesLocation] = CurrentLocation
                && AdjustmentTable[DeliveryLocation] = CurrentSublocation
                && CurrentDate >= AdjustmentTable[EffectiveDate]
                && CurrentQuantity >= AdjustmentTable[MinQuantity]
                && CurrentQuantity <= AdjustmentTable[MaxQuantity]
        )
    )

RETURN
    IF(
        ISBLANK(MatchingAdjustment),
        CurrentTotalCost,  -- No matching adjustment
        CurrentTotalCost + (CurrentQuantity * MatchingAdjustment)  -- Apply the adjustment
    )

2 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Icon for Super User rankSuper User
    1 year ago

    Hi maxf94 ,Can't you do a merge and then add a custom column for this

    • Merge Queries:

      • In Power Query, merge the "Sales Table" with the "Adjustment Charge Table" based on Location and Sublocation.
      • After merging, filter rows where:

        • SalesTable[Quantity] falls between AdjustmentChargeTable[Min] and AdjustmentChargeTable[Max].

        • SalesTable[Date] is on or before AdjustmentChargeTable[Date].

    • Add Custom Column:

      [TotalCost] + [Charge]
      You could do this in Dax also something kind of like this

     

     

    Adjusted_Total_Cost =
VAR CurrentLocation = SalesTable[Location]
VAR CurrentSublocation = SalesTable[Sublocation]
VAR CurrentDate = SalesTable[Date]
VAR CurrentQuantity = SalesTable[Quantity]

RETURN
    SUMX(
        FILTER(
            'AdjustmentChargeTable',
            AdjustmentChargeTable[Location] = CurrentLocation &&
            AdjustmentChargeTable[Sublocation] = CurrentSublocation &&
            AdjustmentChargeTable[Min] <= CurrentQuantity &&
            AdjustmentChargeTable[Max] >= CurrentQuantity
        ),
        AdjustmentChargeTable[Charge]
    ) + SalesTable[TotalCost]
​

     

     

    If this post helped please do give a kudos and accept this as a solution 

    Thanks In Advance

     

  • maxf94's avatar
    maxf94
    Frequent Visitor
    1 year ago

    Hello,

     

    I've managed to sort my issue with the following Calculated Column:

     

    Total Cost With Adjustment = 
VAR CurrentLocation = Sales[SalesLocation]
VAR CurrentSublocation = Sales[DeliveryLocation]
VAR CurrentDate = Sales[TransactionDate]
VAR CurrentQuantity = Sales[Quantity]
VAR CurrentTotalCost = Sales[Total Cost]

-- Find the matching adjustment charge
VAR MatchingAdjustment = 
    CALCULATE(
        FIRSTNONBLANK(AdjustmentTable[ChargeValue], 1),
        FILTER(
            ALL(AdjustmentTable),
            AdjustmentTable[SalesLocation] = CurrentLocation
                && AdjustmentTable[DeliveryLocation] = CurrentSublocation
                && CurrentDate >= AdjustmentTable[EffectiveDate]
                && CurrentQuantity >= AdjustmentTable[MinQuantity]
                && CurrentQuantity <= AdjustmentTable[MaxQuantity]
        )
    )

RETURN
    IF(
        ISBLANK(MatchingAdjustment),
        CurrentTotalCost,  -- No matching adjustment
        CurrentTotalCost + (CurrentQuantity * MatchingAdjustment)  -- Apply the adjustment
    )