PowerBI - Aggregate multiple rows using combination of fixed and variable calculations mapped to ID in separate table

Question

I have a rather complicated data set, but will attempt to simplify it for this post.

I have contract data in table F1_Contract with a simplified format of:

I am attempting to calculate the expected profit from contracts. The first thing that I needed is to calculate the incremental Volume from each contract that was valid between the Current Date and the Next Date, depending upon the date slicer used in the view. After much pain and anguish, someone pointed me to a post on StackOverflow that resolved my issue:

Create a Disconnected Date table, use that as the Date Slicer, then calculate the date difference between the Current Date, START_DATE of the slicer, Next Date, and END_DATE of the slicer.

The resulting Measure is

DELTA DATE =
CALCULATE (
SUMX (
    F1_Contract,
    DATEDIFF (
        MAX ( MAX ( F1_Contract[CURRENT_CONTRACT_DATE] ), [Disconnected_MIN_Date] ),
        MIN ( MAX ( F1_Contract[NEXT_CONTRACT_DATE] ), [Disconnected_MAX_Date] ),
        DAY
    )
),
FILTER (
    F1_Contract,
    F1_Contract[CURRENT_CONTRACT_DATE] <= [Disconnected_MAX_Date]
        && F1_Contract[NEXT_CONTRACT_DATE] >= [Disconnected_MIN_Date]
)
)

I then take that Measure and multiply it by the VOLUME_PER_DAY to get the incremental Volume for the view with the following formula:

Incremental Cumulative VOLUME = 
CALCULATE(SUMX(F1_Contract,F1_Contract[VOLUME_PER_DAY]* [DELTA DATE])) 

To calculate F1 Revenue and F1 Cost, I take the F1 Unit Cost and the appropriate F1 price based on the Incremental Volume and derive the following measures:

Incremental F1 Revenue =
CALCULATE (
    MAX (
        SUMX (
            F1_Contract,
            [Incremental Cumulative VOLUME] * [F1 Sell Rate @ GAD Per Shipment]
        ),
        [Calc F1 MinCharge]
    )
)


Incremental F1 Cost =
CALCULATE (
    SUMX ( F1_Contract, [Incremental Cumulative VOLUME] * F1_Contract[F1_Cost] )
)

This all works great! I can create a report at the ID level, Indicator level, or the Lane level and all of the numbers are correct.

The problem is that I have a second revenue table, F2_Contract_Revenue, that consists of F2 revenues formatted like the following (note there may be 0 to 15 rows in F2_Contract_Revenue for any given ID in F1_Contract)

F2_Contract_Revenue:

Although the ID in F1_Contract is unique, just to be on the safe side I have a separate DISTINCT_ID table that I have used to link the ID from F1_Contract and F2_Contract_Revenue.

Now I need to calculate the F2 revenue for each ID; using a visual formula of:

If(BASIS = “FLAT”, F2_Unit_Rev, MAX(F2_Min, (Incremental Volume * F2_Unit_Rev))

The Measure I created after about 30 attempts is:

F2 Revenue =
CALCULATE (
(
    SUMX (
        F2_Contract_Revenue,
        (
            MAX (
                [Incremental Cumulative VOLUME]
                    * IF ( F2_Contract_Revenue[BASIS] = "RATE",
                           F2_Contract_Revenue[F2_Unit_Rev], 0 ),
                F2_Contract_Revenue[F2_Min]
            )
        )
            + IF ( F2_Contract_Revenue[BASIS] = "FLAT",
                   F2_Contract_Revenue[F2_Unit_Rev], 0 )
    )
),
FILTER (
    F2_Contract_Revenue,
    F2_Contract_Revenue[ID] = RELATED ( F1_Contract[ID] )
)
)

This works correctly at the Lane level. However, in the views at the ID level, it is slightly off (I have not been able to track down why) and at the Indicator level is it exponentially off.

I need to use this in a formula that will be represented as F1 Revenue + F2 Revenue – F1 Cost which is of course also exponentially off at the INDICATOR level (note there are multiple rows of INDICATOR = 1 and a single row of INDICATOR = 2).

The data is proprietary, so I cannot share the PowerBI file, however, I can answer more specific questions with the data that I’ve cleaned up here.

Any advice, thoughts, corrections, help is greatly anticipated and appreciated!!!

Answer 1

[Suggest you show the moedel with Relation direction]

At a quick view of this, I think the problem is the data model - relation direction.

you work fine on [Incremental F1 Revenue] (ID level, Indicator level, or the Lane level). these are all in 'F1 table'.

But, when it comes to [F2 Revenue], you get the Problem.

(measure is involve 'F1 table' & 'F2 table').

FILTER ( F2_Contract_Revenue, F2_Contract_Revenue[ID] = RELATED ( F1_Contract[ID] )

Also you said that you

just to be on the safe side I have a separate DISTINCT_ID table

so I like point out is, may you show your model (dimTable - factTable) for problem shooting.

In Dax, its all about Relation and Model XD (after you know about dax so much, model might be the really problem.)

Answer 2

after reading a lot more posts and going through helpful videos, I determined the problem was in the ordering of the values in my aggregation statement as well as insufficient bounding of the time dimension.
I have three steps a) for each row of the F2 table, determine if the value is a FLAT or RATE; if FLAT, use that value, if RATE, then multiply that value by the dynamic measure that is determining volume based on the report Date Slicer b) compare the FLAT or RATE outcome to a MIN value in the table c) aggregate those values using the link between the F1 and F2 tables.
The calculation that works is: F2 = CALCULATE (SUMX(F2_Contract_Revenue, ((MAX(IF(F2_Contract_Revenue[BASIS]="RATE",F2_Contract_Revenue[F2_Unit_Rev]* [Incremental Cumulative Volume],F2_Contract_Revenue[F2_Unit_Rev]),F2_Contract_Revenue[F2_MIN]))) ) ,FILTER(F2_Contract_Revenue,F2_Contract_Revenue[ID]=RELATED(F1_Contract[ID])) ,FILTER ( F1_Contract, F1_Contract[CURRENT_CONTRACT_DATE] <= [Disconnected_MAX_Date] && F1_Contract[NEXT_CONTRACT_DATE] >= [Disconnected_MIN_Date] ))

I appreciate the response and for the next issue I will create a generic modle that I can post as an example.