# Power BI Exchange

View Only

## Excel Calculations in Power BI

• #### 1.  Excel Calculations in Power BI

Bronze Contributor
Posted Oct 10, 2018 04:29 PM
Edited by Don James Oct 10, 2018 05:02 PM
Hi All,

I have some calculation in excel sheet and l want to implement the same in Power BI.
I need your help with the Dax expression. Below is the logic. The attached doc has the example.

I need a Dax expression to calculate the % profit in the attached doc.

Also how to get the negative values in a bracket. ex -2569856     (2569856)
Any number in a bracket in the attached sheet is negative

Logic

To get the % Profit for each description.

Divide - Bikes/Product Line Profit * 100

17,507,002 / 105,216,979 *100 = 16.7%

Thanks
DJ

------------------------------
Don James
77090
Houston TX
8322005000
------------------------------

Attachment(s)

Items.docx   14K 1 version

• #### 2.  RE: Excel Calculations in Power BI

Posted Oct 10, 2018 06:49 PM
In DAX, you can use the DIVIDE function:

DIVIDE(Measure1, Measure2)

The divide function will handle divide by zero errors natively, which is handy.

To multiply the result of the divide by 100:
DIVIDE(Measure1, Measure2) * 100

To format with negatives in brackets:
Currency format does this natively.
For other data types (like percentages)  - you either have to have negatives show with a dash or concatenate the parenthesis yourself, which turns your number in to a string.
Things might get weird,especially with sorting.

------------------------------
Audrey Abbey
SR. BI Developer/Analyst
New Seasons Market
Portland OR
------------------------------

• #### 3.  RE: Excel Calculations in Power BI

Bronze Contributor
Posted Oct 11, 2018 10:12 AM
Hi Audrey ,
What is the Dax formula to divide each line in the column to by the total , looking at the attached doc.

Any help will do

ThankS
DJ

------------------------------
Don James
77090
Houston TX

------------------------------

• #### 4.  RE: Excel Calculations in Power BI

Posted Oct 11, 2018 10:33 AM
To divide the amount on the row, by the total for the row, you use the same DIVIDE function.

However, your second measure will need to use CALCULATE(Measure2, ALL(Table[Column]), where the Column is whatever you have on rows.

If I have sales laid out by department, with departments showing on each row, it would look like this:

Measure 1 = SUM(Sales[SalesAmount])
Measure 2 = CALCULATE(Measure1, ALL(Depts[DepartmentName]))

The first measure will respect the department context created by the rows, and give you the total sales for that department.
The second measure will ignore the department context created by the rows, filters and slicers, and give you the total sales for ALL departments.

There are several ALL functions available in DAX.
https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

Best,
Audrey

------------------------------
Audrey Abbey
SR. BI Developer/Analyst
New Seasons Market
Portland OR
------------------------------

• #### 5.  RE: Excel Calculations in Power BI

Gold Contributor
Posted Oct 11, 2018 04:19 AM
Hi Don,

To convert the negative values in a bracket. ex -123456789.50 to (123456789.50), you can you use the FORMAT function of DAX.
First, create a new column and then format it by using DAX.
E.g.
`Revenue1 = FORMAT(Sheet1[Revenue],"#,##0.0;(#,##0.0)")`

Here, I have considered a decimal number column but you can change it as per your own requirement.

I have also attached a .pbix with a simple example. Hope that will help you.

Regards,

------------------------------
Data Analyst
Dublin
894164048
------------------------------

Attachment(s)

Report 01.pbix   140K 1 version

• #### 6.  RE: Excel Calculations in Power BI

Bronze Contributor
Posted Oct 11, 2018 04:48 AM
Thanks Tuhin
What's the Dax expression that will divide row by row.
Look at my attachment lm dividing each row by the total to get the %profit of each row.
Can you help with the formula.

Thanks
DJ

------------------------------
Don James
77090
Houston TX
8322005000
------------------------------

• #### 7.  RE: Excel Calculations in Power BI

Gold Contributor
Posted Oct 12, 2018 03:24 AM
Hi Don,

I do not find any attachment. If you send me the attachment then definitely I will try to help you.

Regards,

------------------------------
Data Analyst
Dublin
894164048
------------------------------

• #### 8.  RE: Excel Calculations in Power BI

Bronze Contributor
Posted Oct 12, 2018 10:25 AM

------------------------------
Don James
77090
Houston TX
8322005000
------------------------------