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 ver...
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

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