Average or Sum of measure based on what is filtered,

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Hi guys.

I have a report that I display in PowerBI.

There are numerous measures.
My issue is that some of the measures should be averaged, some should be summed.
Is there any way that I can control if a measure is a "Sum of" or"Average of" based on who is selected in the filter?
Capture.JPG


The filter is on the left - It is a single item filter.
The charts are set up to display the corresponding measures
The Value field (ValueNoError) is the one that I want to change. Sum with one filter but average on the other selection.

Thanks

Thomas
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi,

A dynamic change of the measure should be possible if the list of filters is limited and the filter is truly a single item filter.

e.g. let say you want to change sum of amount to average of amount.

Measurename = CALCULATE(
IF( SELECTEDVALUE('TABLENAME'[Filter columename],"No Single Value")="xxx",
Sum(TABLENAME[Amount]),
Average(TABLENAME[Amount])
))
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Hi,

A dynamic change of the measure should be possible if the list of filters is limited and the filter is truly a single item filter.

e.g. let say you want to change sum of amount to average of amount.

Measurename = CALCULATE(
IF( SELECTEDVALUE('TABLENAME'[Filter columename],"No Single Value")="xxx",
Sum(TABLENAME[Amount]),
Average(TABLENAME[Amount])
))
Thanks. In the meantime I solved this by generating the report so I only get a single value straight out of SAP, so it doesn't matter if it is a SUM or an Average.

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,867
Messages
5,655,691
Members
418,231
Latest member
Jimmy_Jef

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top