Forum Discussion
Calculated Measure with conditions
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 updateSalesAverage = DIVIDE( CALCULATE( SUM(Total Sales $), [AccountCode]=410000 ), CALCULATE ( Sum(Total Sales Quantity), [AccountCode]=420000 ) )
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
)
)
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.
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
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
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.
