Using aggregated value in a calculation.

[weisheng]

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!

[weisheng]

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]

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]

@weisheng yes

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

[weisheng]

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]

@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))