Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Visualize invoicing rate for customers in a ga...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Visualize invoicing rate for customers in a gauge

08-12-2016
07:40 AM

Hi!

I have a problem that i am not able to solve. I have a table containing the following (simplified data):

- Client - Name of the client
- Year
- Month
- IsBillable
- SpentHours

I want to show the invoice % with slicing possibilites on multiple levels in a gauge.

Example of data

Client Year Month IsBillable SpentHours

Client1 2016 1 Yes 12

Client1 2016 1 No 6

Client2 2016 1 Yes 100

For the simplifed example 112 hours was billable and 6 not. That gives us an invoice rate of 112/118=95%

If I only select Client1 the invoice rate should show 12/18=67%

How can I achieve this? Averages on a single field does not give accurate numbers.

Thanks in advance!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-12-2016
12:56 PM

You could do this all in one calculation, but for readability, I like to use building blocks. This will also help if you have to use some of these same measures in other places. Create the following four measures, using whatever names make sense for you:

Hours = SUM(TableName[SpentHours])

Billable Hours = CALCULATE([Hours], TableName[IsBillable]="Yes")

Non-Billable Hours = CALCULATE([Hours], TableName[IsBillable]="No")

Invoice Rate = [Billable Hours] / [Hours]

The non-billable hours measure isn't actually necessary for your end result, but it was useful for this visual:

If you'll never use any of the building blocks elsewhere anyway, you can skip straight to the end result with a single measure:

Invoice Rate = CALCULATE(SUM(TableName[SpentHours]), TableName[IsBillable]="Yes") / SUM(TableName[SpentHours])

With your invoice rate measure selected in the Fields list, you can format it as a percent in the Modeling tab. Click the percent sign, then bump up or down the number of decimals you want to show. This will not affect the actual number in other calculations, i.e., 66.66666% will still be 0.6666666 in further calculations, even if shown as 67%.

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-12-2016
12:56 PM

You could do this all in one calculation, but for readability, I like to use building blocks. This will also help if you have to use some of these same measures in other places. Create the following four measures, using whatever names make sense for you:

Hours = SUM(TableName[SpentHours])

Billable Hours = CALCULATE([Hours], TableName[IsBillable]="Yes")

Non-Billable Hours = CALCULATE([Hours], TableName[IsBillable]="No")

Invoice Rate = [Billable Hours] / [Hours]

The non-billable hours measure isn't actually necessary for your end result, but it was useful for this visual:

If you'll never use any of the building blocks elsewhere anyway, you can skip straight to the end result with a single measure:

Invoice Rate = CALCULATE(SUM(TableName[SpentHours]), TableName[IsBillable]="Yes") / SUM(TableName[SpentHours])

With your invoice rate measure selected in the Fields list, you can format it as a percent in the Modeling tab. Click the percent sign, then bump up or down the number of decimals you want to show. This will not affect the actual number in other calculations, i.e., 66.66666% will still be 0.6666666 in further calculations, even if shown as 67%.

Featured Topics

Top Solution Authors

User | Count |
---|---|

301 | |

144 | |

67 | |

67 | |

40 |

Top Kudoed Authors

User | Count |
---|---|

355 | |

222 | |

107 | |

77 | |

64 |