Average measure does not update calculation when click on slicer.

[Dunner2020]

Hi there,

I have created a measure that calculates the average value of a column named 'VoLL per hour' in the VoLL table. I want to use that measure as line value in Line and stacked column visualisation. The table looks like as follows:

I code the average measure as follows:

Average VoLL Feeder = CALCULATE(

AVERAGE(VoLL[VOLL per hour]),

ALL(VoLL)

)

Now the problem is that when I click on the slicer values (such as season slicer which contains  shoulder winter summer season), the average measure does not update its calculation. Could anyone help me where I am making the mistake?

[v-kelly-msft]

Hi @Dunner2020 ，

Create a measure as below:

Average VoLL Feeder  = 
IF(ISFILTERED('VoLL'[Season]),CALCULATE(AVERAGE('VoLL'[voll per hour]),FILTER(ALL('VoLL'),'VoLL'[Season]=SELECTEDVALUE('VoLL'[Season]))),CALCULATE(AVERAGE('VoLL'[voll per hour]),ALL('VoLL')))

 If above doesn work,could you pls upload your .pbix file to onedrive business and share the link with us?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Dunner2020]

@v-kelly-msft , it same issue. I am posting a picture of my data model. Slicer value coming from the Season table. I think there should not be a problem as long as the relationship is established. 

[nandic · Accepted solution]

@Dunner2020 ,
It was my mistake, didn't understand the issue.

Try using variables for all slicers that should influence the average value (my demo below - i am calculating avg of Sales column in Sales table and filter is from dimension Item):

Average Sales Amount  v2 =

var _selectedItem = ALLSELECTED('Item'[Item Id])

var _selectedRows = FILTER(ALL(Sales),Sales[Item Id] in _selectedItem)

RETURN

CALCULATE(AVERAGE(Sales[Sales]),_selectedRows)

If there are multiple slicers like Item impacting this calculation, add them like variable _selectedItem is created. And then add them to variable _selectedRows like _selectedItem is added.

If all items are selected, avg calculations are same:

If only Items 1 and 3 are selected, yellow line shows avg value for only these items, and blue line is still showing avg for all items.

[mahoneypat]

Please try using ALLSELECTED instead of ALL

Average VoLL Feeder = CALCULATE(

AVERAGE(VoLL[VOLL per hour]),

ALLSELECTED(VoLL)

)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Dunner2020]

@mahoneypat , ALLSELECTED(VoLL) does not produce the right answer. The average value is around 9000 and with ALLSELECTED(VoLL) it gives around 29000.

[mahoneypat]

Please try just removing the filter from Feeder ID with

Average VoLL Feeder = CALCULATE(

AVERAGE(VoLL[VOLL per hour]),

ALL(VoLL[Feeder_ID)

)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Dunner2020]

@mahoneypat 😥😓. did not work

[nandic]

@Dunner2020 ,
Last row makes this formula independent from used slicer on Voll table.
ALL(VoLL) - means return everything, no matter what is selected.

Firstly try removing that part and see how it looks like.
After that you could try adding AllExcept(Voll[Season]) (instead of All(Voll)) and then selecting season filter will influence on calculation.

[Dunner2020]

@nandic , I tried by removing all function. It started giving by an average by feeder id as shown in the figure:

If I include allexcept(VoLL[Season]), it does not make any difference when I apply season slicer on it.