Forum Discussion
Calculated Measure Based on Criteria - Aggregation Issue?
- This isn't a problem with the last year logic in my original reply. Just change the name of DimDate to the appropriate table.
- Really, a goal should be precomputed. In your original question it was simple enough to not care much about how to implement as a measure, but with the details you're revealing, I'd suggest not calculating this as a measure. Your sales last year are known at the time of model refresh, and will never change. These are two defining attributes of values that should be pre-computed. I'd recommend doing this in ETL, either in your source system if you have access/authority to initiate those changes, or as a part of your Power Query step before loading this data to the model. See a sample below for how you might implement this:
Here you go, a nice sample. You can manually enter a multiplier table ('Enter Data') and then copy your sales query and join in the multipliers for each year, then do your multiplication. The result is a FactGoal table that you can then just have some simple measures defined against. This is how we'll set up this sort of reporting for any of our clients.
Sales =
SUM( 'MyTable'[SalesAmount] )
SalesLastYear =
CALCULATE(
[Sales]
,FILTER(
ALL( DimDate )
,DimDate[FiscalYear] = MAX( DimDate[FiscalYear] ) - 1
)
)
SalesGoal =
[SalesLastYear] * 1.12
SalesGoalConditionalDisplay =
SUMX(
VALUES( 'MyTable'[Functional Group] )
,('MyTable'[Functional Group] = "IC - CENTRAL AND WEST"
|| 'MyTable'[Functional Group] = "IC - NORTHEAST AND SOUTHEAST"
) * [SalesGoal]
)This works if you've got a good date dimension (google "power pivot date dimension" for a lot more thorough coverage of the how and why for a date dimension - this conversation is not worth having if you don't have one).
We've got a little bit of trickiness in the SUMX(), but its a general good pattern to learn.
SUMX() will create a row context based on the table passed to it as argument1 by iterating over the rows of that table. It evaluates an expression (argument2) for each row context, and aggregates these with a sum.
VALUES() returns a table of the distinct values in the column or table named as its only argumnet, based on current filter context. In any visual (even tables / matrices, where we see rows), the labels are filter context. Thus, when only one label is in context (like in a table visual's detail rows), we get a 1-row table returned from VALUES(), and the SUMX() in that case is the same as just evaluating the expression in its argument2.
The funkiness we bring is by doing arithmetic with a Boolean value. We perform a Boolean test in the parentheses, checking for the logical or of [Functional Group] being one of the values you're interested in. This returns true or false. False * <numeric expression> = blank. True * <numeric expression> = <numeric expression>. Thus we'll only get [SalesGoal] if the row context in SUMX() is for one of the two names rows.
When only one row is in context we get [SalesGoal] for that row if that row context is for one of the named [Functional Group]s.
When we get to the total, there will be two row contexts that have non-zero results (the two named items) and those will be aggregated with a sum. This gives us the appropriate total behavior for this measure.