Column calculation based on text string

[awitt]

Needing to create a calculated column that takes into account several other columns as well. A sample of my data is below.

Basically I need to take the revenue for the rows with an item version of "S" and evenly distribute that total revenue across non "s" items of the same order number and factor in quantity as well. For order 10001, there is $10 of "s" revnue and it would need to be broken up in $2.00 increments for the 5 total items that were sold. 

For Order 10002, there is $12.00 of "s" revenue that gets distributed amongs the 4 other total items. 

The "s" quantity is always 1. 

Thanks

[judspud]

Hi @awitt 

you can use the all function inside a calculate function to do just this.

The process would be as follows;

Calculate(average, filter(all(TABLE),item version=itemversion))

EDIT: If it is possible to share a copy of your pbix file i would be happy to try and assist in creating such calculation.

Hope this helps

Thanks,

George

[awitt]

That just resulted in averaging the revnue without considering the order # or the quantity at all. Again the goal is to average out only the "S" revenue per order # amoungst all of the non "S" items.

[v-lili6-msft · Accepted solution]

HI, @awitt 

You could use this formula as below:

Column = 
IF (
    Sheet2[Item Version] <> "S",
    CALCULATE (
        SUM ( Sheet2[Per Item Revenue] ),
        FILTER (
            Sheet2,
            Sheet2[Order #] = EARLIER ( Sheet2[Order #] )
                && Sheet2[Item Version] = "S"
        )
    )
        / CALCULATE (
            SUM ( Sheet2[Quantity] ),
            FILTER (
                Sheet2,
                Sheet2[Order #] = EARLIER ( Sheet2[Order #] )
                    && Sheet2[Item Version] <> "S"
            )
        )
)

Result:

Best Regards,

Lin