Calculating revenue based on activities in other rows

[amalm]

Hi, 

I have the following data:

I need to calculate revenues based on the following condition: 

An account is chargeable $1.9 if both activities (A1 & A2) have been performed. It is chargeable $0.6 if only A2 is performed (for some accounts A1 is not required). It is not chargeable anything if only A1 is performed (installation not completed).

A1 & A2 might be entered in the same row (if both are done by the same person in the same instance) or it may appear in different rows if it was done by different people or at different times. 

The result I expect is this:

Please let me know how this can be achieved. Thanks.

[TeigeGao · Accepted solution]

Hi @amalm ,

We can use create the following calculated column directly:

Revenue =
VAR a =
    CALCULATE (
        CONCATENATEX ( Table1, Table1[Activity], " " ),
        FILTER ( ALL ( Table1 ), Table1[Account ID] = EARLIER ( Table1[Account ID] ) )
    )
RETURN
    IF (
        Table1[Activity] = "A1"
            && a = "A1 A2",
        0,
        IF (
            Table1[Activity] = "A2"
                && a = "A1 A2",
            1.9,
            IF (
                Table1[Activity] = "A1"
                    && a = "A1",
                0,
                IF ( Table1[Activity] = "A1 A2", 1.9, 0.6 )
            )
        )
    )

The result will like below:

Best Regards,

Teige

[Ashish_Mathur]

Hi,

A1 and A2 are both performed for Account ID 1.  So Shouldn't the result for both rows be 1.9?

[Anonymous]

Did this in Power Query, so attached the pbix below. but:

• Group the rows by ID and aggregate for All Rows
• Create a new column that uses this group to build a list of activities:

List.Accumulate(
    [All Data][Activity],
    "",
    (state, current) => state & " "&current)

• Expand the table, split the column in bullet #2 
• Remove misc column so we just have ID, All Data and what the Prev activity was

add a custom column with:

if
[All Data.Activity] = "A2" and [Prev] ="A1"
then 1.9
else
if
[All Data.Activity] ="A2" and [Prev] <>"A1"
then
0.6
else
if
[All Data.Activity] ="A1" then 0
else
1.9

and final table:

PBIX File:

https://1drv.ms/u/s!Amqd8ArUSwDS0nIhW0nl7uNLV3iQ

Hope that helps!

-Nick

[amalm]

@Anonymous Thank you, but you seem to have attached a different PBIX file.. Can you please attach the correct one so that I can check it out?

@Ashish_Mathur No, if both rows had $1.9 the totals would be wrong.

[Anonymous]

@amalm 

Sorry about that, this link should work better:

https://1drv.ms/u/s!Amqd8ArUSwDS0nT5n4LHAd3cmgc7