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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
aishaakhter18
Helper I
Helper I

Percentage Calculation in Power BI

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.

aishaakhter18_0-1696009447345.png

 

 

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

 

 

10 REPLIES 10
v-cgao-msft
Community Support
Community Support

Hi @aishaakhter18 ,

Is this your expected output?

vcgaomsft_0-1696310036882.png
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
Frequent Visitor

  • 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
Super User
Super User

hi, @aishaakhter18 

can you provide dax code?

 

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

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

  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%

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:

ronrsnfld_1-1696071291579.png

 

 

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 

 

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]

 

    )

)

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 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors