Power BI Exchange

Expand all | Collapse all

Excel Calculations in Power BI

  • 1.  Excel Calculations in Power BI

    Posted 11 days ago
    Edited by Don James 11 days ago
      |   view attached
    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)

    docx
    Items.docx   14K 1 version


  • 2.  RE: Excel Calculations in Power BI

    Gold Contributor
    Posted 11 days ago
    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

    Posted 11 days ago
    Hi Audrey ,
    Thanks for your reply.
    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

    Gold Contributor
    Posted 11 days ago
    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.
    You can read about them here:
    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

    Bronze Contributor
    Posted 11 days ago
      |   view attached
    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,




    ------------------------------
    Tuhin Tapadar
    Data Analyst
    Dublin
    894164048
    ------------------------------

    Attachment(s)

    pbix
    Report 01.pbix   140K 1 version


  • 6.  RE: Excel Calculations in Power BI

    Posted 11 days ago
    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

    Bronze Contributor
    Posted 10 days ago
    Hi Don,

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

    Regards,

    ------------------------------
    Tuhin Tapadar
    Data Analyst
    Dublin
    894164048
    ------------------------------



  • 8.  RE: Excel Calculations in Power BI

    Posted 10 days ago


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