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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ppgandhi11
Helper V
Helper V

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

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!

View solution in original post

8 REPLIES 8
ppgandhi11
Helper V
Helper V

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.

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!

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.

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!

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

 

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

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!

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

 

 

 

Phil_Seamark
Employee
Employee

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.