cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Slicer filtering when it shouldn't

I am written the following Dax formula to calculate the average max words each vendor can do. When I put that formula into my visual and use a date slicer it is slicing my target value and max value in the gauge visual. I only want the slicer to filter words in progress. Can someone help explain how I can accomplish this?

 

Formula I'm using to get the average This is the formula I am using to calculate the target & max value in my visual

 

Max words (M) =
VAR Vendor = [Vendor]
RETURN
    DIVIDE (
        DataDump1[Max Words Per (M)],
        CALCULATE (
            COUNTROWS ( DataDump1 ),
            ALL ( DataDump1 ),
            DataDump1[Vendor] = Vendor
        )
    )

 

Capture.JPGCapture1.JPG

 

I want the value to change when I slice the date but want to keep the same value for the target and max value. 

 

Thank you for all your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Slicer filtering when it shouldn't

@v-yuta-msft

 

Thank you for responding to me. I was able to get the solution. I created a measure and then reference Max words (M) column. 

 

Formula I used was 

 

Max Words (M) Measure =
CALCULATE (
    SUM ( DataDump1[Max words (M)] ),
    ALLEXCEPT ( DataDump1, DataDump1[Vendor] )
)

 

And the formula I used for my calculated column to get the average of max words was

 

Max words (M) =
VAR Vendor = [Vendor]
RETURN
    DIVIDE (
        DataDump1[Max Words Per (M)],
        CALCULATE (
            COUNTROWS ( DataDump1 ),
            ALL ( DataDump1 ),
            DataDump1[Vendor] = Vendor
        )
    )

 

Thanks for the help and looking into it for me!

 

 

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Slicer filtering when it shouldn't

Hi jarodjp42,

 

Modify your measure like below and check if it can meet your requirement:

Max words (M) =
VAR Vendor = [Vendor]
RETURN
    DIVIDE (
        DataDump1[Max Words Per (M)],
        CALCULATE (
            COUNTROWS ( DataDump1 ),
            ALLSELECTED ( DataDump1 ),
            DataDump1[Vendor] = Vendor
        )
    )

Regards,

Jimmy Tao

Highlighted
Anonymous
Not applicable

Re: Slicer filtering when it shouldn't

@v-yuta-msft

 

Thank you for responding to me. I was able to get the solution. I created a measure and then reference Max words (M) column. 

 

Formula I used was 

 

Max Words (M) Measure =
CALCULATE (
    SUM ( DataDump1[Max words (M)] ),
    ALLEXCEPT ( DataDump1, DataDump1[Vendor] )
)

 

And the formula I used for my calculated column to get the average of max words was

 

Max words (M) =
VAR Vendor = [Vendor]
RETURN
    DIVIDE (
        DataDump1[Max Words Per (M)],
        CALCULATE (
            COUNTROWS ( DataDump1 ),
            ALL ( DataDump1 ),
            DataDump1[Vendor] = Vendor
        )
    )

 

Thanks for the help and looking into it for me!

 

 

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors