Forum Discussion

sfaizee's avatar
sfaizee
Helper I
5 years ago
Solved

Need help to correct averageA formula.

Hi Everyone,

I am very new to DAX and i need help to resolve this problem i am facing with dax. 

I have a table for which i want to calculate the average values.

My measure details are as below:

 

Total price for setup =
CALCULATE(
    AVERAGEA('2012'[Total price]),
    FILTER('2012','2012'[Activity Text STD]="setup"),
    FILTER('2012','2012'[PrI]="5")
)
Note: These 2 filters are on 2 seperate coloumns on the sheet. 
When i use only one filter i am getting the result. But i need both the filters to work. 
Please help. 
Thanks.
 
 
 
 
The syntax is right (no errors). 
But the result of this measure is not showing in the card. 
It gives me an error:
 "Cant display the visual"
 
 
  • Fowmy's avatar
    Fowmy
    5 years ago

    sfaizee 

     

    Why do you use AVERAGEA? and "5"


    Try your measure this way:

    Total price for setup = 
CALCULATE(
    AVERAGE('2012'[Total price]),
    
    FILTER( 
         ALL('2012''2012'[Activity Text STD], '2012','2012'[PrI]),
        '2012'[Activity Text STD]="setup" && '2012','2012'[PrI]=5
    )
)

     

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn




3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi sfaizee 

    1.What else does the error message say?

    2. Can you share the pbix or at least show a sample of your table(s)?

    3. Are you just using this measure in a card or do you have something else in the report (slicers, etc.)?

    4. Why are you using AVERAGEA( )  instead of AVERAGE( )?

    5. Is PrI a column of type number or text?

    Try this:

     

     

     

    Total price for setup =
CALCULATE(
    AVERAGE('2012'[Total price]),
    '2012'[Activity Text STD]="setup",
    '2012'[PrI]=5
)

     

     

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    sfaizee 

     

    Why do you use AVERAGEA? and "5"


    Try your measure this way:

    Total price for setup = 
CALCULATE(
    AVERAGE('2012'[Total price]),
    
    FILTER( 
         ALL('2012''2012'[Activity Text STD], '2012','2012'[PrI]),
        '2012'[Activity Text STD]="setup" && '2012','2012'[PrI]=5
    )
)

     

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn




  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi sfaizee ,

    • The AVERAGEA function takes a column and averages the numbers in it, but also handles non-numeric data types according to the following rules:

      • Values that evaluates to TRUE count as 1.
      • Values that evaluate to FALSE count as 0 (zero).
      • Values that contain non-numeric text count as 0 (zero).
      • Empty text ("") counts as 0 (zero).

    • If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGE function.

    • Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0.

    Based on your description, not certain what is your expected output, you can try to use average function as @ AlB mentioned to test. Basically need more information like some sample data, expected output or screenshots etc. for further discussion.

     

    Best Regards,
    Yingjie Li

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