Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous,

 

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/
Anonymous
Not applicable

Hi @Anonymous,

 

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/
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.