# Variance and Per Day DAX formulas

#### luirib

##### Board Regular
I’m trying to add two measures to my report that will give me the following:
1. An actual to budget variance column. The only trick is that it needs to evaluate the account type and if it is a Revenue account, it needs to do Actual minus Budget, but if it is a Cost or Days accounts it needs to do Budget minus Actual. This is the formula I’ve come up with but DAX is not accepting it (AccountType Code 2 is for Revenue):
=IF(COUNTROWS(VALUES( AccountTypes[AccountType])=1,
IF(VALUES( AccountTypes
Code:
``````)=2 ,
sum(Actual[Amount])-Sum(Budget[Amount]) ,
sum(Budget[Amount])-Sum(Actual[Amount])  ),
0))

2.	A per day ratio for each line item. This is the formula I’ve come up with but, again, DAX is not accepting it (AccountType Code 1 is for Days):
=IF(COUNTROWS(VALUES(
AccountTypes[AccountType])=1,
(sum(Actual[Amount]/sum(Actual[Amount],AccountTypes[Code]=1)
),0))

I know I am close but I’m missing something. Can someone give me a hint as to what I’m doing wrong? Thanks a lot.``````

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### powerpivotpro

##### Board Regular
Can you tell me what the error messages are in each case?

Replies
1
Views
245
Replies
0
Views
1K
Replies
0
Views
204
Replies
0
Views
812
Replies
6
Views
185

1,136,434
Messages
5,675,836
Members
419,586
Latest member
RoteichA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back