Forum Discussion

vbvbvb's avatar
vbvbvb
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

Add filter to show specific values in calculated columns

Hi all,   Trying to solve following task.  I have data in more or less same format, where final goal is to calculate Aveage value based on Month and Year values:   Name of colour Certain Valu...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi vbvbvb，

    Modify the formula to:

    AvgValue Blue =
VAR _table =
    FILTER (
        TableName,
        TableName[Year] = EARLIER ( TableName[Year] )
            && TableName[Month] = EARLIER ( TableName[Month] )
            && TableName[Certain Value] <> BLANK ()
            && 'TableName'[Name of colour] IN { "Blue" }
    )
RETURN
    IF (
        [Name of colour] IN { "Blue" },
        SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table ) )
    )

    I add an if condition in the code, tweak the color in different color columns.

     

    I attach my sample below for your reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

vbvbvb's avatar
vbvbvb
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

Thanks v-yanjiang-msft , I will try that approach. Just extra quesiton, how  this condition can be expanded to multiple values, for example Red + Blue + Green (and many more)? Does this method scale up?

            && 'TableName'[Name of colour] = EARLIER ( TableName[Name of colour] )

  

vbvbvb's avatar
vbvbvb
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

No, this doesn't work - I need something like this :

Your calculation doesn't filter text values of colour column - I basically need average calculation to happen only if there's hard match of 1 or many values (up to 15 values).

 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi vbvbvb ,

    If you want to custom the color, tweak the formula like this:

    AvgValue Green+Blue =
VAR _table =
    FILTER (
        TableName,
        TableName[Year] = EARLIER ( TableName[Year] )
            && TableName[Month] = EARLIER ( TableName[Month] )
            && TableName[Certain Value] <> BLANK ()
            && 'TableName'[Name of colour] IN { "Green", "Blue" }
    )
RETURN
    SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table ) )

    You can see, in the formula I only want to calculate the average of Green and Blue, get the correct result:

    You can tweak the formula according to your condition.

     

    I attach my sample below for your reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • vbvbvb's avatar
      vbvbvb
      Icon for Microsoft Employee rankMicrosoft Employee
      3 years ago

      When I add  this part, column still returns all values for all rows regardless of what actual value 'TableName'[Name of colour] has:

       

       

       

       

       

                  && 'TableName'[Name of colour] IN { "Green", "Blue" }

       

       

       

       

      It doesn't exclude , for example "Red"  from "AvgValue Green+Blue" like in your example, despite DAX and inputs being identical. 

       

      It doen't work even in your PBIX example, if change value to only "Blue", it shows "Blue" and "Green": 

      And if change to "Red" and "Blue", it shows all values: 

       

      It seems that filter becames invalid when "month" and "year" are matching :

       

      If there any way to make more explicit filter which filters colour value first and then from it's results looks at month and year for average calculation?

       

       

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi vbvbvb，

        Modify the formula to:

        AvgValue Blue =
VAR _table =
    FILTER (
        TableName,
        TableName[Year] = EARLIER ( TableName[Year] )
            && TableName[Month] = EARLIER ( TableName[Month] )
            && TableName[Certain Value] <> BLANK ()
            && 'TableName'[Name of colour] IN { "Blue" }
    )
RETURN
    IF (
        [Name of colour] IN { "Blue" },
        SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table ) )
    )

        I add an if condition in the code, tweak the color in different color columns.

         

        I attach my sample below for your reference.

        Best Regards,
        Community Support Team _ kalyj

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.