Forum Discussion
Issue displaying average on a line chart and table
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.
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
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
Measure Utilisation = SUM(Utilisation[ColumnUtilisation])/[CountOfProducts] - sum of utilisation divided by the number of product types to give me the average figure I need.