cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
creynolds
Helper V
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 )

Capture.PNG

1 ACCEPTED SOLUTION

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.

 

Untitled.png


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

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
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/

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.

 

Untitled.png


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

View solution in original post

This is great -- Thanks for your help!!

You are welcome.


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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors