Good day to all! It's been about a year since I've been in Power Pivot and my DAX is a little rusty--I've been yanking my hair out for two days so I'm finally reaching out to the community in hopes that someone can assist. Here's the scenario:
A worksheet contains an 'Overall Health' measure-based field in a pivot table. This measure is to be calculated using criteria based on other finance-based measures within Power Pivot. The criteria are as follows:
If a category has a percentage variance greater than -.15% and less than.15%, then GREEN.
If a category has a dollar variance greater than -2000 and less than 2000, then GREEN.
If a category has percentage variance out of range BUT the dollar variance is within range, then GREEN.
Otherwise RED.
Here's where it gets interesting: Each measure references a separate 'category' of financial breakdown as in this example (2/5 categories shown here):
CAP Variance Last Month: $5,998
CAP Variance % Last Month: 100.0%
EXP Variance Last Month: $1,651
EXP Variance % Last Month: 150.0%
The measures providing each category of data must have their 'health' evaluated according to the criteria above--any RED in any category will set the 'Overall Health' to RED.
What I've tried so far:
I went the 'sloppiest' route first, and just tried a combination of nested IF statements in different orders, but I kept getting stopped by this--the evaluation just wouldn't continue if say, it found exception in one category (and set it to GREEN), but another category contained criteria that would thereafter require it to be RED.
Then I tried something more creative. I set up a unique measure for each Category and metric: 10 measures consisting of 5 'sets' of "Percentage Health" and "Dollar Health". Each measure 'set' containing the following:
EXP Variance % Health:=IF([EXP Variance % Last Month]>=-.15&&[EXP Variance % Last Month]<=.15,1,8)
EXP Variance Health:=IF([EXP Variance $]<=2000&&[EXP Variance $]>=-2000&&[EXP Variance % Last Month]>=-.15&&[EXP Variance % Last Month]<=.15,1,IF([EXP Variance $]>=-2000&&[EXP Variance $]<=2000&&([EXP Variance % Last Month]<=-.15||[EXP Variance % Last Month]>=.15),99,0))
Then I put the outputs into a string (1/5 sets shown):
[CAP Variance % Health]&[CAP Variance Health]&....
Resulting in something like:
0089911110
Finally, a 'decoder' measure was used to search for 0's or 8's which were defined to identify "RED" statuses.
Measure 1:=IF(FIND("0",[Forecast Accuracy Health (Calculated)],1,0)>=1,"R",IF(FIND("8",[Forecast Accuracy Health (Calculated)],1,0)>=1,"R","G"))
This convolution worked but the refresh time due to all the calculations was painful.
I'm certain there is a simpler, more elegant solution. Any suggestions are welcome, I am open to different approaches, methods, you name it. Thank you so much for taking a look, as always I appreciate the community and its help. If you have questions, let me know, thank you again!!!
Before creating the main measure,you need to create the individual helper measures for each financial category. These will return 1 if the conditions for GREEN are met, otherwise 0.
For CAP:
CAP % Condition:= IF(AND([CAP Variance % Last Month] >= -.15, [CAP Variance % Last Month] <= .15), 1, 0)
CAP $ Condition:= IF(AND([CAP Variance Last Month] >= -2000, [CAP Variance Last Month] <= 2000), 1, 0)
CAP Health:= IF(OR([CAP % Condition] = 1, [CAP $ Condition] = 1), 1, 0)
For EXP:
EXP % Condition:= IF(AND([EXP Variance % Last Month] >= -.15, [EXP Variance % Last Month] <= .15), 1, 0)
EXP $ Condition:= IF(AND([EXP Variance Last Month] >= -2000, [EXP Variance Last Month] <= 2000), 1, 0)
EXP Health:= IF(OR([EXP % Condition] = 1, [EXP $ Condition] = 1), 1, 0)
This process needs to be repeated for each category. Now, the main measure:
Overall Health:=
IF(
AND([CAP Health] = 1, [EXP Health] = 1, /* Other category health conditions */),
"GREEN",
"RED"
)