Calculating Variance and Counting Projects where variance exceeds a threshold

[Anonymous]

Hi,

I am currently working on a project where we are looking to calculate variance based on the current phase of a project.  The phase of the project will determine which field we will use to calculate variance.  We then want to count how many projects have a variance of -25% or less for a card visual.

Here is the DAX measure I am using to calculate variance: 

Variance = SWITCH(TRUE(),

SELECTEDVALUE('Project_Dimension'[Current_Phase]) in {"Analyze", "Design", "Build", "Test", "Deploy", "Warranty" }, DIVIDE((SUM('Project_Financial_Measures'[Total Cost Original BC]) - SUM('Project_Financial_Measures'[Total Cost Actual Forecast])),SUM('Project_Financial_Measures'[Total Cost Original BC])),

DIVIDE((SUM('Project_Financial_Measures'[Total Cost Planning Exit]) - SUM('Project_Financial_Measures'[Total Cost Actual Forecast])),(SUM('Project_Financial_Measures'[Total Cost Planning Exit]))))

Here's the DAX I've tried to count the projects who have a variance of -25% or less (which is not working, only returns '(Blank)' in my card visual:

Var Count = CALCULATE(DISTINCTCOUNT(Project_Financial_Measures[Project_Name]),FILTER(Project_Financial_Measures, 'Project_Financial_Measures'[Variance] <= -.25))

And here's the data:

Project_Name	Current_Phase	Total Cost Original BC	Total Cost Planning Exit	Total Cost Actual Forecast	Variance
A	Build	592521.05	460114.01	282297.42	52%
B	Test	1343921.37	0	1967024.76	-46%
C	Build	546309.74	2179285.31	2229822.96	-308%
D	Test	4284925.73	0	5044821.18	-18%
E	Test	462841.29	406979.68	465773.78	-1%
F	Build	0	0	1763135.35
G	Warranty	0	0	139069.15
H	Build	1201453.52	1386096.59	2335053.08	-94%
I	Deploy	2793309.9	2376018.52	2305232.77	17%
J	Warranty	5661652.63	0	2910610.68	49%
K	Deploy	2959146.84	0	1395860.18	53%
L	Analyze	283818.26	0	364650.3	-28%
M	Warranty	734891.71	0	480726.67	35%
N	Analyze	840096.09	0	701790.37	16%
O	Analyze	1132573.29	0	1254232.61	-11%
P	Build	13587750.45	0	19104613.89	-41%
Q	Build	488215.13	1517078.09	1702372.68	-249%
R	Unassigned	602142.61	0	580180.06
S	Analyze	605978.11	0	576400.18	5%
T	Design	2125887.67	0	2231014.99	-5%
U	Warranty	946499.53	0	460891.4	51%
V	Unassigned	198973.85	0	151644.86
W	Deploy	12377561	0	3480023.24	72%
X	Analyze	105487.17	0	6561280.02	-6120%
U	Deploy	772233.79	559127.35	634068.53	18%
Z	Warranty	1982269.6	0	352243.23	82%
AA	Design	1017186.43	0	4309828.23	-324%

Any help would be greatly appreciated, thank you in advance!

-Jackson

[amitchandak]

@Anonymous , Try a measure

countx(values(Table[Project_Name]), if([Variance]>-.25, [Variance], blank()))

or

Sumx(values(Table[Project_Name]), if([Variance]>-.25, 1, blank()))

[wdx223_Daniel · Accepted solution]

@Anonymous 

Var Count = CALCULATE(DISTINCTCOUNT(Project_Financial_Measures[Project_Name]),FILTER(SUMMARIZE(Project_Financial_Measures, Project_Financial_Measures[Project_Name],'Project_Dimension'[Current_Phase]),'Project_Financial_Measures'[Variance] <= -.25))