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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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,106,165
Messages
5,509,551
Members
408,741
Latest member
dramqueenuk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top