cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ppgandhi11 Member
Member

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-

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Counting Percentage problem.

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

8 REPLIES 8
Phil_Seamark Super Contributor
Super Contributor

Re: Counting Percentage problem.

HI @ppgandhi11

 

Why not just add another calculated column using your formula.

 

new column = 'table'[GoodSale] * 100 / 'table'[TotalSale]

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

ppgandhi11 Member
Member

Re: Counting Percentage problem.

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.

Phil_Seamark Super Contributor
Super Contributor

Re: Counting Percentage problem.

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

nickchobotar Established Member
Established Member

Re: Counting Percentage problem.

@ppgandhi11

 

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  --

 

 

 

ppgandhi11 Member
Member

Re: Counting Percentage problem.

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.

Phil_Seamark Super Contributor
Super Contributor

Re: Counting Percentage problem.

HI @ppgandhi11

 

For number 2, you could try this

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

ppgandhi11 Member
Member

Re: Counting Percentage problem.

yes, it worked for #2. Thanks a lot for pointing it out.

 

Is there a way to address 1 and 3? Thanks again!

Phil_Seamark Super Contributor
Super Contributor

Re: Counting Percentage problem.

HI @ppgandhi11

 

Not so sure about #1 but can these settings help you with #3?

 

2018-03-23_8-19-25.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 414 members 4,446 guests
Please welcome our newest community members: