Problem with sum of measure

[FredrikR_SO]

I have the following table (simplified)

• Customer Name
• Billable hours
• Non billable hours
• Billable amount
• Average billable rate (measure SUM(Billable amount)/SUM(Billable hours))
• Estimated non billable amount (measure SUM(Non billable hours)*Average billable rate

I present this in a table and add a visual filter to hide rows where "Non billable hours" is 0
The table sums it up nice where the total of "Estimated non billable amount" is correct

I then add a Card only showing the total "Estimated non billable amount" it then shows another amount. It is not allowed to filter this visual the same way as the table

Since not all customers have Non billable hours the measure calculates different average billable rates in the table and in the Card. How can I fix this?

Thanks!

[v-stephen-msft · Accepted solution]

Hi @FredrikR_SO ,

I created sample data based on your description.

Visual filter

visual filter = IF(MAX('Table'[Non billable hours])<>0,1)

Average billable rate = SUM('Table'[Billable amount])/SUM('Table'[Billable hours])

Estimated non billable amount = SUM('Table'[Non billable hours])*[Average billable rate]

It should be 33.95 instead of 33.62.

You can get the correct result by creating a new measure.

Total = SUMX('Table',[Estimated non billable amount])

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

just a quick thought whilst I'm on my phone but could you try something like this:

Estimated non billable amount = calculate(SUM(Non billable hours)*Average billable rate, filter(table,table[Non billable hours]>0))

[FredrikR_SO]

Thanks, I actually tried that but for some reason Estimated non billable amount = NaN in that case