Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago

Amount Spent on Modifiers

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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Sean's avatar
Sean
Community Champion
9 years ago

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's avatar
    ElliotP
    Post Prodigy
    9 years ago

    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's avatar
      Sean
      Community Champion
      9 years ago

      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!:smileyhappy:

       

       

       

       

       

      • ElliotP's avatar
        ElliotP
        Post Prodigy
        9 years ago
        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.