Forum Discussion
Helper IUse a variable in a measure as a filter within same measure
I have a measure that I need to take one step further, but can't figure out the solution to make it work. Using the below measure I can determine the variance for each customer. However, I only want to return the results if the variance shows underperformance (i.e. variable Underperforming >0). Is it possible to add this last filter to the measure so that only underperforming values are picked up? Each time I try a filter, it's looking for a table, but I want the filter to be on the variable itself. Currently it's retruning True or False, but I want the actual dollars and only where it's underperfoming.
Total Month Expected vs Actual =
VAR
Underperforming =
CALCULATE(
[Month Expected]-[Month Actuals],
FILTER(Table1,[Frequency]="Only Once") ,
FILTER(Table2,[Date]=Table3[Max Expected Date]))
Return
Underperforming>0
13 Replies
- Zubair_Muhammad
Community Champion
Do you need this???
Total Month Expected vs Actual = VAR Underperforming = CALCULATE ( [Month Expected] - [Month Actuals], FILTER ( Table1, [Frequency] = "Only Once" ), FILTER ( Table2, [Date] = Table3[Max Expected Date] ) ) RETURN IF ( Underperforming > 0, underperforming )
ChuckChuckThanks, Zubair_Muhammad! Almost, but not exactly. I got this far too, but gave up on this version of the measure because won't actually display the grand total of just the underperforming values. If in a table I can get the expected results to appear, but the grand total for the table is still showing the total without that last filter applied (so ignoring if it's underperforming or not). I also have a card visual and since the net of all accounts is not underperforming, the value displayed in the card is not correct.
- Zubair_Muhammad
HI ChuckChuck
Normally you can use this pattern to get correct totals for the MEASURE
Try creating another MEASURE on these lines
New Measure = IF ( HASONEFILTER ( TableName[ColumnName] ), [Total Month Expected vs Actual ], SUMX ( ALLSELECTED ( TableName[ColumnName] ), [Total Month Expected vs Actual ] ) )
