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...
  • 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
        )
)

     

     

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      I want to thank Anonymous too. He did a lot of the work.

  • abhi1707's avatar
    abhi1707
    New Member
    8 years ago

    AnonymousAnonymousWhat if i want to apply where condition on categorical variable, say account status= Yes or No, want to calculate for those accounts whose status is No. Pls advise.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      abhi1707

      It's similar, depends on a datatype of your categorical variable.

      If it's string then just use smth like: CALCULATE(SUM([Amount]),Account_Status="No")

      or this:

      CALCULATE(SUM(Fact_Table[Amount]),Filter(Accounts,Accounts[Account_Status]="No"))

      in case you have two connected tables

      Michael

      • ericOnline's avatar
        ericOnline
        Post Patron
        6 years ago

        Does anyone know how to refer to a record the user is interacting with rather than a static value?

        Example:

        - Table visualization that acts as a slicer

        - User clicks a record in the table

        - Card visualization with a Measure set to:

        measure = 
CALCULATE(
    SUM(Fact_Table[Amount]),
    Filter(Accounts,Accounts[Account_Status]=SELECTED.ACCOUNT_STATUS)
)

        Where "SELECTED.ACCOUNT_STATUS" is the ACCOUNT_STATUS column in the Table visualization.

        - I dont' want to create a measure for every single value of a given column.

        - If there were 15 statuses, would I need 15 measures or is there a way to dynamically filter based on user selection?

        Thank you