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
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] )
)

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

Hi popov, Unfortunately this version still isn't producing the desired results. In fact, when I add a table to just show the measure for each customer name the table is excluding records and the measure doesn't add up correctly. Here's a screenshot: