How to calculate the percentage of total items using the count of a group

[mbailey]

I'm trying to figure out how to calculate the percentage of total items the count of a group is. Example data below. Each contract can have one of several possible serv_type values. In the example below there are 15 total items and 7 of them are FS.  So the FS's are .46 % of the total count (7/15).

I'm using a Direct Query so I can create another intermediary table for this.  Ultimately I'd like to create a visualization showing the resulting percent of total items for each serv_type group. 

contract | serv_type
16718 | FS
16716 | FS
16715 | PM3
16713 | FS
16712 | FS
16711 | FS
16710 | FS
16709 | FS
16708 | WA
16707 | WAP
16706 | WA
16705 | PM3
16704 | WAP
16703 | WA
16702 | WA

Thanks,

Mike

[hthota]

Report Pic:

[hthota]

Hi.

This would help you @mbailey

Report Link: https://app.powerbi.com/groups/me/reports/f691344c-4c18-450b-b95a-d2a32a0ccee0?ctid=470e1ac4-5b55-48...

[mbailey]

Hi,

This link doesn't work for me. It just keeps "spinning" saying Working/Loading.

Mike

[hthota]

Is it the requriment you need.

[mbailey]

Yes, but the link you sent explaining how to do it doesn't work.

Mike

[hthota]

Get the file from this Link.

I hope i would help you.

Link: https://drive.google.com/open?id=1XE9NBo03E8zRsJe3F7AliGMxjXPwrO4o

[mbailey]

Hi,

Thank you for sharing your report, but unfortunately, that will not work.  First, as I mentioned, I'm using a Direct Query - connected to a database with live data that constantly changes. In your example, you created a table with two extra columns that I don't have - Count, and Total Type Count. PB will also not net you create additional "work" tables when your starting source is a Direct Query. I also can not create additional columns. You get the error "Function 'COUNT' is not allowed as part of a calculated column DAX expressions on DirectQuery models."

I need some kind of formula/measure that will first group and then return the count of each serv_type.  I can then use that to calculate the percentage.  In plain language: DIVIDE( the count of serv_type grouped by serv_type, the total Contracts count)

Mike