- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Counting Percentage problem.
Hi, I am very new to power bi. trying to get hold on the functionalities. I have following dataset.
Customer TotalSale GoodSale
ABC 120 90
DEF 200 156
:
:
Both TotalSale and GoodSale are calculated columns. I used the Add Column functionality to get to this point. Please let me know if you need to know more details about TotalSale and GoodSale formula I have used. I think they are irrelevant so I am not digressing.
I want to add a column/measure that calculates the percentage of GoodSale against TotalSale. So final output should be something like:
Customer TotalSale GoodSale %GoodSale
ABC 120 90 75 (90*100/120)
DEF 200 156 78 (200*100/156)
How to achieve this? Further, I want to color code it. if the %GoodSale is > 70 then color it green, otherwise color is red.
Any help is appreciated. Thanks.
Prashant-
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The conditional formatting features don't currently support using measurse to determine the back/font colur.
You can use the code to generate arrows or other characters (thumbs up/down etc).
Let me know if you'd like help with that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I figured out the first part. I created new measure and then I used the Calculate function as below.
Measure = calculate(sum(GoodSale)/TotalSale * 100)
This gives me the desired output in the last column.
The conditional formating is still outstanding. I don't know how to get that done. I have created another measure called ColorCode
ColorCode = if(%GoodSale > 70, "Green", "Red")
This gives additional column at the end that prints Green and Red based on the %GoodSale column value. However, I want to print the text "Green" in Green color and "Red" in Red color. How to get that done? Any idea? Thanks a lot.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The conditional formatting features don't currently support using measurse to determine the back/font colur.
You can use the code to generate arrows or other characters (thumbs up/down etc).
Let me know if you'd like help with that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have one more question before closing this out.
While using a Line Chart for percent Good sale for Customers, I see 0-20-40-60-80-100 as pre defined percentage that shows up on vertical axis (y axis). Is there a way to:
1. display 0-10-20-30-40... 100 for percentages?
2. A horizontal dotted line at 70% that clearly shows who is above 70 and who is below 70%?
3. Every customer (x axis) should have his own dot on the graph where it belongs. Today, if i move the cursor i see a pop up value for each customer on the line, but I want to keep a visible dots for each customer. It is easily done in excel. I am looking for that in Power BI.
Thanks a lot.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
yes, it worked for #2. Thanks a lot for pointing it out.
Is there a way to address 1 and 3? Thanks again!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I would suggest you to use a DIVIDE() function with its powerful alternative value feature as a third argument.
Percentage =
DIVIDE(
SUM(Table1[GoodSale]),
SUM(Table1[TotalSale ]),
BLANK() // will return blank if error
)
You can set the percentage in the Modeling menu and use conditional formatting for your coloring as @Phil_Seamark suggested.
Nick --
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI @ppgandhi11
Why not just add another calculated column using your formula.
new column = 'table'[GoodSale] * 100 / 'table'[TotalSale]
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
07-30-2024 01:07 PM | |||
10-18-2024 09:06 AM | |||
10-01-2024 01:42 AM | |||
04-13-2023 06:26 AM | |||
10-07-2024 02:16 PM |
User | Count |
---|---|
100 | |
69 | |
42 | |
37 | |
30 |
User | Count |
---|---|
157 | |
89 | |
62 | |
46 | |
40 |