Forum Discussion
Advocate IIIssue displaying average on a line chart and table
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 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 typesMeasure Utilisation = SUM(Utilisation[ColumnUtilisation])/[CountOfProducts] - sum of utilisation divided by the number of product types to give me the average figure I need.
5 Replies
- amitchandak
Super User
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
- AnthonyTilley
Solution Sage
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
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 typesMeasure Utilisation = SUM(Utilisation[ColumnUtilisation])/[CountOfProducts] - sum of utilisation divided by the number of product types to give me the average figure I need.
