Issue displaying average on a line chart and table

[Daveed1973]

I have a Power BI desktop file which I use to display utilisation of products. When I filter to just an individual product the figures are correct. However I need to show the average for multiple products and as soon as I select more than one product I get odd results.

I have tried doing this with both columns and measures but get the same result.

The image below shows the figures correct when only one product is selected.

Whereas this image shows odd results when more than one product is selected.

What I would like to see on the charts is the average utilisation across the two products, which by my calculations should be 86% (146 divided by 169).

I'm sure the issue may be to do with filtering and my measures not being configured correctly so this is how I have configured my measures.

MeasureUiU = SUM(Utilisation[ColumnUIU])

MeasureContracted = AVERAGE(Utilisation[ColumnContracted])

MeasureUtilisation = Utilisation[MeasureUiU]/Utilisation[MeasureContracted]

Simply selecting the ColumnUtilisation and changing it from sum to average doesn't work so I'm really unsure how to get the chart and table to show the correct average for utilisation.

Any help would be greatly appreciated.

[AnthonyTilley]

can i ask why you are using Avarage in the Measurecontracted colunm 

this is where your issue lies

the result is actually correct but the maths is wrong 

your total utalisation is probably correct so lets assume it is in your example you have 

33

113

total 146

but in your avarage contracted you have 

48

121

total 103

this is because of the maths around your avarage figure 

if for example i had 6 rows (3 for each products) as below 

Product	Contacted
A	10
A	10
A	10
B	20
B	20
B	20
Total	90

the avarage of A is 10 and the Avarage of B is 20 but the total is not the sum of the two (30) it is the avaraage of all 6 which is 15 

in your example when you take the avarage of the individual products it is correct and the avarage in teh total is correct but what you are after is actually the sum of the lines above 

so just to clarify why are you using avarage is this becasue there are muliple lines with the same value so if you where to use sum the values would be incorrect 

if you can share your PBIX file or a sample of it i can take a look and see if i can correct your equations 

[Daveed1973 · Accepted solution]

@AnthonyTilley  I was using the average because I was getting a total figure which was really high and therefore making the utilisation around 1%. I have managed to fix this issue by doing the following.

Column UiU gives me the number of products in use.

Column ContractQty gives me the number of products they are contracted to use.

Measure CountOfProducts = DISTINCTCOUNT(Utilisation[ProductType]) - gives me the number of distinct product types in the current filter

Column ColumnUtilisation = Utilisation[UiU]/Utilisation[ContractedQty] - gives me the sum of the utilisation for the product types

Measure Utilisation = SUM(Utilisation[ColumnUtilisation])/[CountOfProducts] - sum of utilisation divided by the number of product types to give me the average figure I need.

[amitchandak]

Is it made like this ?

MeasureUtilisation = Sum(Utilisation[MeasureUiU])/Sum(Utilisation[MeasureContracted])

[Daveed1973]

---

@amitchandak wrote:

Is it made like this ?

MeasureUtilisation = Sum(Utilisation[MeasureUiU])/Sum(Utilisation[MeasureContracted])

---

I cannot set it this way as I am trying to refer to measures and it just throws an error.

[amitchandak]

Try

MeasureUtilisation = divide(Sum(Utilisation[MeasureUiU]),Sum(Utilisation[MeasureContracted]))

Please let us know what error u are getting