Forum Discussion

weisheng's avatar
weisheng
Frequent Visitor
8 years ago

Using aggregated value in a calculation.

Hello all,

 

I am relatively new to Power BI. 

 

In my current project, I have a column of monthly usage. I know that I can pick different aggrated values such as Sum, or Average and it will show at the bottom of the column. I was wondering if I can use that number in a equation for a calculated column. 

 

 

Thank you!

5 Replies

  • weisheng's avatar
    weisheng
    Frequent Visitor
    8 years ago

    Hello all,

     

    I am relatively new to Power BI. 

     

    In my current project, I have a column of monthly usage. I know that I can pick different aggrated values such as Sum, or Average and it will show at the bottom of the column. I was wondering if I can use that number in a equation for a calculated column. 

     

    Thank you!

                                                                                       

    • Thejeswar's avatar
      Thejeswar
      Super User
      8 years ago

      Hi weisheng,

      As you see, the Aggregated Values show at the bottom of the table. But it is not possible to use them directly into some other expression or calculated column

       

      Instead you can use that by manually specifying in a DAX statement, for ex. in your case, suppose you want to find the difference between the Total monthly usage and Average monthly usage for each month, you can create and use a measure like the one below

       

      Difference = AVERAGE(Table1[Usage]) - SUM(Table1[Usage])

      Here AVERAGE(Table1[Usage]) holds the same value as your aggregated column total. i.e. 773.25

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    weisheng yes

     

    avg of monthly usage = calculate(avg(table1[monthly usage]), all(table1))

    • weisheng's avatar
      weisheng
      Frequent Visitor
      8 years ago

      Thanks ! But I am looking to get the value in the red circle and I have a slicer that picked a specific product. So your equation actually returns the average of every monthly usage amonst all product. Im not sure if I am explaining it well.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        weisheng yeah not sure if I can understand from your explanation, you need to provide better raw data, and examples of what is the output you need or provide a power bi other it is impossible to give the right ans in first go.

         

        I am going to take another shot in the dark and say but if you want specific to a product then you use

        avg of monthly usage by product = calculate(avg(table1[monthly usage]),allexcept(table1,table1[product]) 

         

        if you just want the avg of monthly usage and then have it filtered by multiple slicers try this one

         

        avg of monthly usage = calculate(avg(table1[monthly usage]),allselected(table1))