Forum Discussion

jaymanivannan's avatar
jaymanivannan
Frequent Visitor
9 years ago
Solved

Calculated Measure with conditions

Hi,

I am new to PowerBI. I am trying to create a calculated measure based on the condition. For Example, I am trying to create measure for the following condition

 

SalesAverage= ((SUM(Total Sales $) Where AccountCode=410000) / (Sum(Total Sales Quantity) where AccountCode=420000))

 

Please help me to get this done.

 

Thanks.

 

  • Anonymous's avatar
    Anonymous
    9 years ago

    jaymanivannan response is great.  To add to this, rather than using the / symbol and have to make a divide by zero check, just use the Divide() function.  I've also set this out using a spacing format, which is a good habit to get yourself into.

    Here is an update

    SalesAverage = DIVIDE(
        CALCULATE(
                SUM(Total Sales $), 
                [AccountCode]=410000
        ),
        CALCULATE (
                Sum(Total Sales Quantity), 
                [AccountCode]=420000
        )
)

     

     

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi,

    You should use the CALCULATE function.

    Your formula should look something like this:

    SalesAverage:= CALCULATE (SUM(Total Sales $), [AccountCode]=410000) / CALCULATE (Sum(Total Sales Quantity), [AccountCode]=420000).

    I would also add division by zero condition check

    Michael

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      jaymanivannan response is great.  To add to this, rather than using the / symbol and have to make a divide by zero check, just use the Divide() function.  I've also set this out using a spacing format, which is a good habit to get yourself into.

      Here is an update

      SalesAverage = DIVIDE(
        CALCULATE(
                SUM(Total Sales $), 
                [AccountCode]=410000
        ),
        CALCULATE (
                Sum(Total Sales Quantity), 
                [AccountCode]=420000
        )
)

       

       

      • jaymanivannan's avatar
        jaymanivannan
        Frequent Visitor
        9 years ago

        Anonymous,  This is working !!!. Thanks for the quick response. No Zero divide error. Thank you once again.

  • Im_SD's avatar
    Im_SD
    New Member
    3 years ago

    Hello!

    How do you write this code in DAX?

    Select column1

    from table1

    Where column3>column2;

    (Consider column1 as text field and column 2 and 3 as value field).