Forum Discussion

KayCon's avatar
KayCon
Frequent Visitor
3 years ago

Dax Measure - Average including filter

Morning 

 

I've calulated an avg. value, now I need to create 3 more split by type, I need this to be displayed on one line chart.

 

Can anyone help with the dax formular to add a filter on to this

 

AvgValue = SUM('Details'[Paid]) / SUM('Details'[ZCount])

3 Replies

  • MAwwad's avatar
    MAwwad
    Solution Sage
    3 years ago

     

    To create additional measures split by type, you can use the FILTER() function to filter your data based on the value of the type column. Here's an example of a DAX formula that calculates the average value split by type:

     

    Copy code
    AvgValueType1 = CALCULATE( SUM('Details'[Paid]) / SUM('Details'[ZCount]), FILTER('Details', 'Details'[Type] = "Type1") ) AvgValueType2 = CALCULATE( SUM('Details'[Paid]) / SUM('Details'[ZCount]), FILTER('Details', 'Details'[Type] = "Type2") ) AvgValueType3 = CALCULATE( SUM('Details'[Paid]) / SUM('Details'[ZCount]), FILTER('Details', 'Details'[Type] = "Type3") )
     

    Each of these formulas will return the average value for a different type, "Type1", "Type2" and "Type3" respectively, this way you can display them on the same line chart.

  • KayCon's avatar
    KayCon
    Frequent Visitor
    3 years ago

    Thank you for your help - got this error when I tried 

     

     The following syntax error occurred during parsing: Invalid token, Line 2, Offset 50, 'On Street"))))))).

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      hi KayCon 

      Be careful about unclosing brakets and the DataSlayer's code shall work. Can you make a screenshot of the issue?

      Or try a simplified code like this:

      AvgType1 = CALCULATE([AvgValue], Details[Type] = "Type1")
AvgType2 = CALCULATE([AvgValue], Details[Type] = "Type2")
AvgType3 = CALCULATE([AvgValue], Details[Type] = "Type3")

       

      p.s. please consider @someone, if you seek further suggestions.