Sum of order line items that don't contain a specific line item

[tchristy99]

Help!

I have a table that holds sales line items and the below matrix that splits them into categories (FF, FF Ship, IN, SW, etc.), each line item belongs to a category but an order can contain items with different categories. I need to create a measure that shows me the total sales of orders that only have items with the category "FF".

In the below example, the total would be $38 because the bottom order has items that belong to FF Ship.

I have tried everything I can think of, any help is appreciated!

Thanks.

[amitchandak]

@tchristy99 , You can use a visual level filter . or have measure like

//[sales] is you exiting measure

new sales = calculate([sales],Table[category] = "FF")
new sales = calculate([sales],filter(Table,Table[category] = "FF"))

or

new sales = calculate(sum(Table[sales]),Table[category] = "FF")

[amitchandak]

@tchristy99 , saw your last update. Did not get it completely.

Can you share sample data and sample output in a table format?

[tchristy99]

Hi @amitchandak & @Anonymous thank you guys for your help. 

Here is some sample data:

And here is a sample output from that data:

Let me know if this is sufficent.

Thanks again!! 

[amitchandak]

@tchristy99 , like this will work ?

new sales = calculate(sum(Table[price]),left(Table[category],2) = "FF")
new sales = calculate(sum(Table[price]),filter(Table,left(Table[category],2) = "FF"))

or you can use containsstring 

https://docs.microsoft.com/en-us/dax/containsstring-function-dax

new sales = calculate(sum(Table[price]),filter(Table,containsstring (Table[category],"FF")))

[tchristy99]

@amitchandak no it will not. Because it will still pull the FF items for customer 22870, but I want those to be omitted because there are items from FF Ship. And the LEFT() function won't work because there are categories that don't start with "FF". 

[V-lianl-msft · Accepted solution]

Hi @tchristy99 ,

Create a calculated column to determine whether each customer contains only FF.

col =
CALCULATE (
    LASTNONBLANK ( 'Table'[category], 1 ),
    FILTER ( 'Table', 'Table'[customer] = EARLIER ( 'Table'[customer] ) )
)

Then create the measure.

Measure = 
CALCULATE (
    SUM( 'Table'[price]),
    FILTER ( 'Table','Table'[col]="ff")
)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tchristy99 , can share some bigger set of data in table format with expected output. if, Left and containsstring both do not work need to see input ve output

[tchristy99]

@amitchandak here is a larger data set and an example of the measure that I would like to get out of it

[Anonymous]

Hi @tchristy99 

_CALC = CALCULATE(SUM(table[Total_column],table[category] = "FF")

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

Regards,
Pranit

[tchristy99]

Hi @Anonymous that won't work because it will add all the "FF" values even if there are other items on the order that are not "FF".

[Anonymous]

Hi @tchristy99 

Can you reply with the table structure you have with data

Regards,

Pranit