Forum Discussion

ChuckChuck's avatar
ChuckChuck
Icon for Helper I rankHelper I
8 years ago

Use 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 ...
Need Help
ChuckChuck's avatar
ChuckChuck
Icon for Helper I rankHelper I
8 years ago

Hi popov, I made some test data and applied the measure to it and then created some screen grabs. This is close to what I need, but it is picking up the values for dates I'm not interested in and it's not giving me a total, just blank. 

 

 

 

popov's avatar
popov
Icon for Resolver III rankResolver III
8 years ago

Hi, ChuckChuck

Try this formula

Total Month Expected vs Actual =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Sheet1, Sheet1[Customer Name], Sheet1[Expected Date] ),
        "UnderPerforming", CALCULATE (
            [Month Expected] - [Month Actuals],
            FILTER ( Sheet1, [Frequency] = "Only Once" )
        )
    ),
    IF ( [UnderPerforming] > 0, [UnderPerforming] )
)