Calculate Average based on filters of a visual

[Anonymous]

Hi, I am new to Power BI, I need help with the following funtionality.

I have a visual which will show total Size based on last 6 cycles:

Reference Visual

The average will change every cycle as it considers the previous 6 cycles based on the current cycle excluding the current cycle.

Eg: If the current cycle is 13 it will show Size for 7-12.

If the current cycle is 12 it will show Size for 6-11...

The cycles data is coming from Table A and the Size data is coming from Table B.

I need to use the average (shown as 40 above for current cycle) of these size values in other visuals.

Can you please help me capture this average somewhere so that it can be reused for other visuals?

Thanks in advance! 🙂

[lbendlin]

Please read up on the concept of contexts, in your particular case filter context.

Your averaging formula will "just work"  on the same page because the filtering you do on your chart visual (showing just the six items) will impact all other visuals on that same page that are downstream in the data model.

Your table A needs to control table B. Here is some pseudo code for your measure

Result = average(selectedvalues(B))

[Anonymous]

Hi @lbendlin , thanks for the response. I have kept the Interaction off for the entire 1-page report.

I only want a way to capture the average from just this 1 particular visual so that I can show that number somewhere else.

In the above case, the Measure should store 40. This value 40 might change in the next cycle.

[lbendlin]

What is your reasoning for keeping the interaction off?

[Anonymous]

@lbendlin , The user doesn't want the data to change based on some other visual. There are particular requirements for filters on each visual. I just need to show that AVerage number, say 40 on top of 2 other visuals. But the data shown inside the visual is not to be chnaged.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Since you have closed the interaction between each visual, the filter context will not be passed between them, you need to hard-code the current cycle to all measures.

I suggest you use what if parameter in your measure ,so you don't need to change all the measures when you need to change the current cycle. https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[lbendlin]

Then you need to do it all via measures. In your case you would need to do 

Divide(topn(7,...)-topn(1,...),6) to get the average of the past six items.