Forum Discussion

vbvbvb's avatar
vbvbvb
Microsoft 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 colourCertain Value Month YearAverage Value
Red1Oct2022Calculation which shows avearge value based month and year
Red4Nov2022 

Green

5Sept2022 
Green2Aug2022 
Blue 3Oct2022 
Blue4Oct2022 

 

This caluclation now is done via following DAX column:

 

AvgValue =
VAR _table =
    FILTER (TableName, TableName[Year] = EARLIER (TableName[Year]) && TableName[Month] = EARLIER (TableName[Month]) && TableName[Certain Value] <> BLANK())
RETURN
    SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table )

 

How can I add additional filter to this expression to filter also per name of colour, i.e Red, Green, Blue, so it would be average value per colour value per month+year?

I tried to create 3 additional columns per each colour and add filter values like && TableName[Name of Colour] = "Red" to merge them later into cominbed column , but I still get all values populated in regardless of text value in filter and they are wrong anyways.

 

What would be  correct way to implement such filtering? Appreciate all advice and help!

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.

     

7 Replies

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

    Hi vbvbvb ,

    According to your description, here's my solution. Modify the formula like this:

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

    Get the correct result:

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

         

  • vbvbvb's avatar
    vbvbvb
    Microsoft Employee
    3 years ago

    That works, thank you very much for all advice!