cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daveed1973 Regular Visitor
Regular Visitor

Issue 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.

1.jpg

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

2.jpg

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.

 
1 ACCEPTED SOLUTION

Accepted Solutions
Daveed1973 Regular Visitor
Regular Visitor

Re: 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 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.

 

Inked2019-09-17 (2)_LI.jpg

 

 

View solution in original post

5 REPLIES 5
amitchandak Super Contributor
Super Contributor

Re: Issue displaying average on a line chart and table

Is it made like this ?

MeasureUtilisation = Sum(Utilisation[MeasureUiU])/Sum(Utilisation[MeasureContracted])
AnthonyTilley Established Member
Established Member

Re: Issue displaying average on a line chart and table

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 

ProductContacted
A10
A10
A10
B20
B20
B20
Total90

 

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 Regular Visitor
Regular Visitor

Re: Issue displaying average on a line chart and table


@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 Super Contributor
Super Contributor

Re: Issue displaying average on a line chart and table

Try

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

Please let us know what error u are getting

Daveed1973 Regular Visitor
Regular Visitor

Re: 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 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.

 

Inked2019-09-17 (2)_LI.jpg

 

 

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 1,626 guests
Please welcome our newest community members: