Amount Spent on Modifiers

[ElliotP]

Morning,

I'm trying to calculate the amount of money spent on specific modifiers in my dataset. I've attached my pbix so it's a bit easier to work with: My Pbix

For each transaction there are multiple rows if there are multiple products, yet each transaction contains a unqiue payment ID. I'm trying to work out for example how much money was spent on the modifier "bacon". This is in the modifiers applied column in 'itemdetailsdogfood$". At the moment I have this code as well as I've tried just dragging and dropping on Page 4 to create what I'm trying yet I can't seem to capture the Bacon's when there are other modifiers as well. The code below is apart of my second idea after my first basic drag and drop try.

P1WTest = 
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 3,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )
RETURN
    (
        CALCULATE (
            COUNT( 'itemdetailsdogfood$'[Modifiers Applied] ), FILTER('itemdetailsdogfood$', 'itemdetailsdogfood$'[Modifiers Applied] = "bacon" ||  "bacon,"),
            
                ExtendedCalendar[Year] = CurrentYear,
                     'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum
            
        )
    )

This code idea being, count the number of times Bacon appears anywhere in the modifiers applied column whether it's by itself or with other modifiers and then simply combine the count output with the price of the modifier in my 'Sheet1' table to arrive at my solution.

But I can't seem to get the OR part working so it filters by "bacon" as well as "bacon,".

Since there will be a lot of variants it would be ideal if I didn't have to created individual measures for each modifier, but I'm sorting choosing battles.

Long story short; I'm trying to calcualte the total amount spent on modifiers to my dishes.

[Sean]

@ElliotP

I have not looked at your file...

However one thing stands out immediately in the formula

you need to reference the column again in the FILTER...

FILTER('itemdetailsdogfood$', 'itemdetailsdogfood$'[Modifiers Applied] = "bacon" || 'itemdetailsdogfood$'[Modifiers Applied] = "bacon,")

[ElliotP]

Thanks fore the response; That solved my issue with the measure not being accepted, but it's still only showing 3 bacons instead of the 5 I'd like. hmmm

[Sean]

@ElliotP

Okay now that I've looked at your file - there's nothing wrong with the Measure now that you added the column name!

You are getting 3 because that's the correct answer for the way the Measure is written!

The table filters correctly for all "bacon" and "bacon," rows in the [Modifiers Applied] column

However you don't have any rows containing only "bacon,"

you do have 2 "Bacon, Lettuce" which are ignored because they also contain Lettuce after Bacon, - so not a match

Note that FILTER does NOT perform a SEARCH inside the column for "bacon, *" (* wild character) it just looks for a match!

Hope this helps.

Good Luck!

[ElliotP]

tester = CALCULATE (
            COUNT( 'itemdetailsdogfood$'[Modifiers Applied] ), FILTER('itemdetailsdogfood$', 'itemdetailsdogfood$'[Modifiers Applied] = "bacon" || 'itemdetailsdogfood$'[Modifiers Applied] = "bacon,*"),

How would I be able to do that and simply count any occurence of bacon regardless of if it's by itself or accompanied by "onion, bacon, cabbage" for example.

I tried using the * and that didn't help lols; I had a look at the search function, but I wasn't sure how I could apply that.

[ElliotP]

Thoughts?