Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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
        )
    )

 


 

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!

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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!

     

     

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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!