cancel
Showing results for
Search instead for
Did you mean:
Helper V

Calculating Percentage of Unique Count

I need help to build a calculation to support the highlighted column.    I need to calculate the total # of options found for a unique count of options.    I need for the highlighted column (below) to divide the Unique Count by the Unique Job.   In this example, the first row should come out to 42.8% (3 divided by 7).    The following represent each of the calculations:

Unique Count = COUNT('options selected'[Option Code])

Unique Jobs = DISTINCTCOUNT('options selected'[Drees Job Number])

Here is my attempt at calculating Unique Job:   % of Unique Job = DIVIDE ( [Unique Count] | 'options selected'[Unique1] )

Here is what makes up Unique1:       Unique1 = DISTINCTCOUNT('options selected'[Drees Job Number])

As a side note, the last column is working just fine; here is its calculation:

% Of Builder Category = VAR this_Unique =
SUM ( 'options selected'[Unique] )
VAR group_unique =
CALCULATE (
SUM ( 'options selected'[Unique] )|
ALL ( 'options selected'[Option Code]| 'options selected'[Option Description] )
)
RETURN
DIVIDE ( this_unique| group_unique )

1 ACCEPTED SOLUTION
Super User

Hi @creynolds,

The mistake you committed was that you had created "% of unique jobs" as a calculated column rather than as a measure.  I changed that to a measure.  Furthermore, i computed something called Unique2 and used that as the denominator in your "% of unique jobs" calculation.  I think the subtotal % is still incorrect but i will await your comments on that one.  Here is the file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
5 REPLIES 5
Super User

Hi,

It is difficult to correct the mistake without looking at your PBI file.  Share the link from where i can download your PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper V
Super User

Hi @creynolds,

The mistake you committed was that you had created "% of unique jobs" as a calculated column rather than as a measure.  I changed that to a measure.  Furthermore, i computed something called Unique2 and used that as the denominator in your "% of unique jobs" calculation.  I think the subtotal % is still incorrect but i will await your comments on that one.  Here is the file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper V

This is great -- Thanks for your help!!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements

Check it Out!

Click here to read more about the December 2021 Updates!

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors