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
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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 ]
    )
)
ChuckChuck's avatar
ChuckChuck
Icon for Helper I rankHelper I
8 years ago

Hi Zubair_Muhammad. I've added the second measure to reference the first. However, the results are simply adding up the total of all values and not just the total where the customer is underperforming to expectations. 

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

    Hello,
    If I correct undrestud, you are tracking performance by Customer. Try this approach (in example I use dimension table Customer with attribute Customer Name):

     

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

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

      Thanks, popov. I've given this a try, but the resutls where only returned for one of the 15 customers and those results weren't correct. If I remove the SUMX part and just do the ADDCOLUMNS part, I get an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." Do you know what this indicates?

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

        Hello, ChuckChuck
        Sorry for my delay. Can you share your formula (based my formula) and screenshots for current and expected result?