Percentage Calculation in Power BI

[aishaakhter18]

I need to calculate the percentage based on the number of programs with a "Yes" response and how many are planned or have a "No" response. However, the challenge is that each program consists of multiple service orders, each with its own Service Order Status, which can be "Yes," "No," or "Planned."

Data Example:

PROGRAM CODE	Service Order #	Service Order Status
ABC1234	IA	Planned
ABC1234	1B	Planned
ABC1234	1c	Yes
ABC1234	2D	Yes
ABC1238	4A	Planned
ABC1239	7A	Under Assessment
ABC1240	2C	Under Assessment
ABC1241	2F	Under Assessment
ABC1242	7C	No
ABC1243	8C	No
ABC1244	2D	Under Assessment
ABC1240	4D	Under Assessment
ABC1240	6D	Under Assessment
ABC1242	9D	Yes
ABC1243	1D	Under Assessment
ABC1242	1H	Yes
ABC1243	2H	Planned
ABC1238	3H	Under Assessment
ABC1239	IJ	Yes
ABC1238	5J	Yes
ABC1239	6Y	Yes

Here's the tricky part: If a program has a "Yes" response for one service order but a "No" or "Planned" response for another service order under the same program, we should count it as a "Yes" for that program and disregard the other responses.

1. Service Order Status Response = Yes: This counts as a "Yes" for a program.
2. Service Order Status Response = Planned: This only considers unique program numbers without other responses.
3. Service Order Status Response = No: This also only considers unique program numbers without other responses.

The goal is to ensure that when we add up the percentages, it should total 100%. Currently, my results show that the percentage with "Yes" is accurate, but the percentages for the other categories are incorrect.

Please advise on how I can achieve this accurately. Thank you.

[v-cgao-msft]

Hi @aishaakhter18 ,

Is this your expected output?

Please check the attachment.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ronrosenfeld]

• Providing results from data you have not posted is not useful. If the results I show do not match the data you did post, then I cannot help you further. . Good luck.

[Dangar332]

hi, @aishaakhter18 

can you provide dax code?

[aishaakhter18]

I have not use DAX for it, I am using %GT calculation in X Axis to get percentage, but t giving me incorrect one

[ronrsnfld]

What would you consider the correct result? How are you handling the "Under Assessment" option? I get very different results for "Yes"

[aishaakhter18]

1. Service Order Status Response = Yes: This counts as a "Yes" for a program.
2. Service Order Status Response = Planned: This only considers unique program numbers without other responses.
3. Service Order Status Response = No: This also only considers unique program numbers without other responses.
4. Service Order Status Response = Under Assesment: This also only considers unique program numbers without other responses.

 The total percentage for all respnses should ne equal to 100%

[ronrsnfld]

You are only restating part of your original post and not providing any percentages. 

How does that indicate your expected results? I thought the expected results would be a percentage for each status. But you don't show how they are derived.

What if a program has, for example, status responses of "No", "Planned" and "Under Assessment" for the different Service Order #'s?

You really need to be more specific. I cannot figure out, for example, how you derive 81% as the correct answer for "Yes".

How do calculate 81% "Yes"

What result would you expect for each of the Planned / Under Assessment / No categories.

The closest I can come, trying to use your written description, is:

[aishaakhter18]

In my real data, i have total # number of programs = 45

1. Out of 45  Programs , 37 Service Order Status Response = Yes: 
2. Out of 45  Programs , 16 Service Order Status Response = Under Assesment 
3. Out of 45  Programs , 3 Service Order Status Response = Planned 

And if you take a percentahe then you will get the Chart I shared with you, In some senarios I have no so need to keep one sanario for it too 

[aishaakhter18]

Can you help me to resolve one more thing, I will greatly appreciate this

When I am using the two DAX see below and putting it in pie chart to get the percentage,  what its doing its counting the total response of RA and then taking % out of it 

One col for  program are and  I col with for insight that have responses - yes, Planned, Under Ass, or blank.  in  programs col there are many duplicates and because of this for Insight col one prog may have Yes and also Planned or Under Ass. 

Now see the two Dax below : and what its doing now 

take total Number of Insight divide by total # of Yes response and that not I am looking for 

Its should give percentage again total # of programs  like this :

Total # of prog (distinct values) divide by total # of Yes response to give me right percentage 

how to fix this 

Dax # 1 

RA Insigts Yes Count = COUNTROWS(

    SUMMARIZE(

        FILTER('my tabel', 'my table[Insights for RA] = "Yes"),

        'my tabler'[PROGRAM]

    )

)

Dax # 2 

RA Insight NoPlannedUA_CountExcludeYes = COUNTROWS(

    SUMMARIZE(

        EXCEPT(

            FILTER(

                'my table',

                'my table'[Insights for RA] IN {"No", "Planned", "Under Assessment", " "}

            ),

            FILTER(

                'my table',

                'my table'[Insights for RA] = "Yes"

            )

        ),

        'my table'[PROGRAM]

    )

)

[aishaakhter18]

Also I have total # of programs are 45 , and if Yes are 37 then left over that should 45-37= *, Only 8 program should be in Planned or under assesmet status, and that what I am trying to figure our how to calculate thos correctly that the prog that are already have yes ststus should not be count again for other status