Calculating a percentage within a distinct count

[fionabee]

I'm currently using the following measure in a visual:

CALCULATE(COUNT(Table1[Status]),Table1[Status]="Accepted")/[Number]

In this measure "Number" was originally a count of all case numbers so the % would reflect correctly but I need "number" to reflect a distinct count so the above is now not calulating the percentage correctly as in this example below: 

Case	Sub Type	Outcome
Case 1	Sub 1	Accepted
Case 1	Sub 2	Accepted
Case 2	Sub 1	Rejected
Case 2	Sub 2	Rejected
Case 3	Sub 1	Rejected
Case 3	Sub 2	Rejected
Case 3	Sub 3	Rejected

it would be counting 2 accepted statuses out of 3 cases so would display 67% where I want it to reflect that 33% of the 3 cases have an accepted status. Please could someone help me to include the distinct count element in the above measure?

[v-karpurapud]

Hi @fionabee 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @fionabee 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @fionabee 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @fionabee 

Welcome to the Microsoft Fabric Forum. Also, thank you @DataNinja777 for the helpful response.

To calculate the percentage of distinct cases where Status is blank , you can simply adapt the previous measure by checking for blank values in the Status column.

DAX

Outstanding Case % :=

VAR OutstandingCases =

    CALCULATETABLE(

        VALUES(Table1[Case]),

        ISBLANK(Table1[Status])

    )

RETURN

DIVIDE(

    COUNTROWS(OutstandingCases),

    CALCULATE(DISTINCTCOUNT(Table1[Case]))

)

If this information helps resolve your issue, kindly consider marking this response as the Accepted Solution, as it may assist other community members facing similar challenges.

Regards, 
Microsoft Community Support.

[DataNinja777 · Accepted solution]

Hi @fionabee ,

To calculate the percentage of distinct cases that have at least one "Accepted" status, you need to shift from counting rows to identifying unique case IDs that meet your condition. Using a filtered table with CALCULATETABLE allows you to isolate the distinct case values where at least one row has a status of "Accepted." Then you divide that count by the total number of distinct cases to get the correct percentage.

Accepted Case % =
VAR AcceptedCases =
    CALCULATETABLE(
        VALUES(Table1[Case]),
        Table1[Status] = "Accepted"
    )
RETURN
DIVIDE(
    COUNTROWS(AcceptedCases),
    CALCULATE(DISTINCTCOUNT(Table1[Case]))
)

In this approach, AcceptedCases creates a virtual table of unique Case values where the status is "Accepted". COUNTROWS(AcceptedCases) gives the number of such cases, and the denominator uses CALCULATE(DISTINCTCOUNT(...)) to get the total number of distinct cases. This ensures that each case is only counted once, and only if it includes an accepted status. For your example, it returns 1 accepted case out of 3, or 33%.

Best regards,

[fionabee]

Hello, this seems to work perfectly! Final question, could you please show me how to write the same for when the status field is blank? So in my visual I can show a % of how many are outstanding?