Average or Sum of measure based on what is filtered,

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
70
Office Version
365
Platform
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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
70
Office Version
365
Platform
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,930
Messages
5,508,176
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top