Forum Discussion

amethyst_tang_2's avatar
amethyst_tang_2
Frequent Visitor
2 years ago
Solved

how to find average

hi i would like to find out the average profit of each genre for the past 10 years . 

i have done power BI features like :
using calculate, sum and filter to found out about the sum of gross that year.

i am now looking for ways to find the average of this, how can i do that?

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi amethyst_tang_2 ,

    Try this DAX to create a measure:

    Measure = AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Released_Year] >= 2010 && 'Table'[Released_Year] <= 2020
    ),
    'Table'[Gross]
)

    And the final output is below:



    Or you can build a slicer to filter the data from 2010 to 2020:

    And now you can just use this DAX to calculate:

    Measure 2 = AVERAGE('Table'[Gross])

    The output will change dynamically according to the slicer you select:


     

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

5 Replies

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi amethyst_tang_2 ,

        Try this DAX to create a measure:

        Measure = AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Released_Year] >= 2010 && 'Table'[Released_Year] <= 2020
    ),
    'Table'[Gross]
)

        And the final output is below:



        Or you can build a slicer to filter the data from 2010 to 2020:

        And now you can just use this DAX to calculate:

        Measure 2 = AVERAGE('Table'[Gross])

        The output will change dynamically according to the slicer you select:


         

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    amethyst_tang_2 Well in theory you just replace the SUM or SUMX function in your formulas with AVERAGE or AVERAGEX. Otherwise, not enough detail to really provide a solution. Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi amethyst_tang_2 ,

    Here is my sample data:

    I use this DAX to create a new measure:

    Average Profit by Genre = 
CALCULATE(
    AVERAGE('Table'[profit]),
    FILTER(
        ALL('Table'[Year]),
        'Table'[Year] > YEAR(NOW()) - 10
    )
)

    The final output is like below:

    If your data table contains only the first 10 years of data, then the DAX only needs to be simplified to:

    Average = AVERAGE('Table'[profit])


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