Can we add average line in table visual?

[Anonymous]

Can we add average line with respect to a measure in table visual in Power BI?

Please help with the issue urgently

[Coriel-11]

I know this is an old thread, but having originalIy come here for help, I found a way of doing this so thought it might be worth sharing. It's fiddly!

Essentially what you have to do it make the background transparent (or remove) and then add another chart behind it. This chart is a stacked column chart with the y axis reversed (so it comes down from the top, rather than up from the bottom as normal) where the main category is "invisible" (i.e. white or the background colour) and the 2nd category is very thin so as to resemble a line. Get rid of all axes, labels,gri lines etc.. The hardest part is making the stack wide enough to cover all your data points. Power BI hates this for some reason so you may have to use several of these charts in the background to cover all your points.

I needed 3 measures:

GreaterThan = IF( [Clicks per entry] >= CALCULATE ( AVERAGE('Themes'[Total clicks] ), ALL( 'Themes'[Theme] ) ) ,"Y")

AveMean Invis = COUNTX(SUMMARIZE(Themes,Themes[Theme]),[GreaterThan])-[AveLine Line]

AveLine Line = 0.1

The first works out if the selection (her a line) is greater than the average and is then used in the second to give the number of lines greater than average.The third is just the thickness of the line. If you have widely varying numbers of lines you might want to tweak this a little. I've used this in the second measure as well, purely for cosmetic reasons.

So then you put measures 2 & 3 into the stacked column chart in the background, change the settings as detailed above and this then should vary as the main table does, even if you add slicers.

Hope that helps

@Anonymous- I know you probably worked around this long ago, but if you're still active it might be helpful for others if you mark this as a solution (assuming, of course, it does what you were wanting to do).

Thanks

Matt

[Anonymous]

The average of ratio column is 85.36% so the line depicts product above the average nad below the average. I've already added measures to my table visual in power BI. 

Please note in visual above, apart from Product ID, Orders recd, Orders shipped and issues, rest all the measures
where Ratio = shipped/received

I need average of ratio to be depicted as line in table visual. Kindly help!

[amitchandak]

@Anonymous , Line in table visual?

You can have avg measure if you need

avg measure =  calculate([measure], allselected())

or

avg measure =  calculate([measure], all(Table))

[Anonymous]

How do I add this then in my table visual as average line?

[amitchandak]

@Anonymous , can you share what do mean by a line, can share some reference screenshot.

You can add measure to table visual

[Anonymous]

[amitchandak · Accepted solution]

@Anonymous , I doubt I have seen something like this. Good Idea to log.

https://ideas.powerbi.com/ideas/

or explore custom visual - https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals

[Anonymous]

Thanks for your help! 🙂