Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Community Champion
7 years ago
Solved

Slicers Not Filtering?

Hi!  I have a question about something weird I'm hoping someone could give me some insight in why...   I have a table populated with customer names along with some financial info and nine slicers. ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    In some of the measures you are checking to see if should have a value, just need to change that from 0 to BLANK()

     

    Contribution Margin Per Case = 
    IF(
        SUM('Raw Data'[Volume]) = blank(),
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Contribution Margin]),
            SUM('Raw Data'[Volume]),
            BLANK()
        )
    )

Net TGP Per Case = 
    IF(
        SUM('Raw Data'[Volume]) <= 0,
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Net TGP]),
            SUM('Raw Data'[Volume]),
            BLANK()
        )
    )

Contribution Margin Rate = 
    IF(
        SUM('Raw Data'[Net Sales]) <= 0,
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Contribution Margin]),
            SUM('Raw Data'[Net Sales]),
            BLANK()
        )
    )

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Any chance you can upload some sample data? Onedrive work well

littlemojopuppy's avatar
littlemojopuppy
Community Champion
7 years ago

Hi!  You should be able to download the PBIX file from here

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    In some of the measures you are checking to see if should have a value, just need to change that from 0 to BLANK()

     

    Contribution Margin Per Case = 
    IF(
        SUM('Raw Data'[Volume]) = blank(),
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Contribution Margin]),
            SUM('Raw Data'[Volume]),
            BLANK()
        )
    )

Net TGP Per Case = 
    IF(
        SUM('Raw Data'[Volume]) <= 0,
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Net TGP]),
            SUM('Raw Data'[Volume]),
            BLANK()
        )
    )

Contribution Margin Rate = 
    IF(
        SUM('Raw Data'[Net Sales]) <= 0,
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Contribution Margin]),
            SUM('Raw Data'[Net Sales]),
            BLANK()
        )
    )

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion
      7 years ago

      BOOM!  Thank you!!!

       

      I feel kind of dumb for not checking those...  :smileyfrustrated:

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        7 years ago

        So...I was reluctant to ask earlier why was everything still showing up despite the slicer filtering.  But I didn't want to seem even more dumb.  After thinking about it, I'm going to answer my own unasked question so I feel slightly less dumb: it's because the measure was actually producing a value (maybe not necessarily a valid value, but still a value).  And because there was a value it was going to show up.

        Thanks again for your help!