Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Julia Streatfield]

Hi everyone

I hope you’re all well and coping in the current climate.

I have a problem with using Average which I’m hoping someone can help with.  I have a bar chart and associated table visualisations in a report.  When I click on one of the axis categories in the bar chart, the table data reflects the selection, which is great.  However I have a new table in the report which has the following:

* the measure ‘Registration’ is a Countrows filter on data in another table (so each row in my main table has a Registration value)
* a calculated column ‘Registration Rate’ which is a % value based on ‘Registration’ measure divided by ‘Target’ whole number in the main table
* a column ‘KPI’ in the main table = IF(‘tablename'[Registration Rate] < Average(‘tablename'[Registration Rate]), “Low”, “High”)

In the table Visualizations tab I’ve right clicked and set the Registration Rate field to Average which gives me a nice total on the data.

I’ve checked the KPI column returns the correct Low or High value when no filters are used on the data and when I select one of the categories in my chart, the data in all tables adjusts accordingly including the Average Registration Rate total.  My problem is the KPI calculation still runs off the average total of all data, not just the selected category. 

I’ve tried putting a filter on the visual to look only at that category as well as a filter on the page as a whole but the KPI column still looks at the Average total of all data.

Any ideas please?  I tried to work out if I could style the data in groups in the same way I might have done in SSRS but grouping doesn’t seem to work in any way the same as in SSRS, as well as trying to work out if I can change the ‘Average of Registration Rate’ filter on the visual and apply that to a single category but there doesn’t seem to be any ‘advanced’ option there.

I hope someone has a bright idea for me out there!  Trying to prove using PowerBI and getting people off their excel spreadsheets is my 2020 goal at the moment.

Look forward to hearing from you.

Cheers
Jules

——————————
Julia Streatfield
CRM Business Intelligence Manager
CBI
London
020 73958214
——————————

[Mark Houchin]

Hi Jules,

You should be able to wrap you average measure with the calculate function and use allselected.

Regards
Mark

——————————
Mark Houchin
——————————
——————————————-