Forum Discussion

awitt's avatar
awitt
Helper III
7 years ago
Solved

Column calculation based on text string

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

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

3 Replies

  • judspud's avatar
    judspud
    Solution Supplier
    7 years ago

    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's avatar
      awitt
      Helper III
      7 years ago

      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's avatar
        v-lili6-msft
        Community Support
        7 years ago

        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