Variance and Per Day DAX formulas

luirib

Board Regular
Joined
Sep 21, 2005
Messages
56
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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top