Calculated Measure with conditions

[jaymanivannan]

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.

[Im_SD]

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

[Anonymous]

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 · Accepted solution]

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

[abhi1707]

@Anonymous@AnonymousWhat 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]

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

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

[Anonymous]

@ericOnline   I believe you would need to use https://docs.microsoft.com/en-us/dax/username-function-dax and then find some way of relating that information to your account information.  If the email address is shared, then you might have some luck.

[ericOnline]

Thanks for the reply @Anonymous , but I'm not sure how that function would help. I'm attempting to grab a column from a Table vis that the user has selected. Then use that column as a variable for a measure. 

[Anonymous]

 Ok i definately misunderstood.  I dont believe there is a dax can interact with visual selections so directly.  You might need to use indirect methods such as HASONEFILTER.

[jaymanivannan]

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

[Anonymous]

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