Sort filtered column by another column value.

[awitt]

How do i seperate the far right column to show the sum per order # instead of the total for both? All of the values in "Column" for Order# 10001 should be 10 and 10002 should be 12.

[Anonymous]

What about

Column =
CALCULATE (
    SUM ( Sheet2[Per Item Revenue] ),
    FILTER (
        ALL ( Sheet2 ),
        Sheet2[Item Version] = "s"
            && Sheet2[Order #] = SELECTEDVALUE ( Sheet2[Order #] )
    )
)

[awitt]

Thanks, unfortunately that just returned all blanks.

[Anonymous]

What about this?

Column =
CALCULATE (
    SUM ( Sheet2[Per Item Revenue] ),
    FILTER (
        ALL ( Sheet2 ),
        Sheet2[Item Version] = "s"
            && EARLIER ( Sheet2[Order #] )
    )
)

If that doesn't work, try removing the ALL function:

Column =
CALCULATE (
    SUM ( Sheet2[Per Item Revenue] ),
    FILTER ( Sheet2, Sheet2[Item Version] = "s" && EARLIER ( Sheet2[Order #] ) )
)

[awitt]

That's what I had. 😕

[Anonymous]

Ok, try removing the ALL function. I had an alternate formula in my last post (I added it as an edit), see if that works.

[awitt]

Still getting the total revenue for all "s" revenue instead of it sorting by Order #. 

[Anonymous · Accepted solution]

Try this :

Column = CALCULATE(SUM(Table1[Revenue]),ALL(Table1), Table1[Order#]= EARLIER(Table1[Order#]), Table1[Item Version]="S")

Thanks
Raj

[awitt]

Awesome, thanks!

[Anonymous]

Got it (I think):

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

[Anonymous]

Good job @Anonymous , Your logic was right, just syntax needed some correction.

Thanks

Raj