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

ChuckChuck

 

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 )

 

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

Thanks, 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'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] )
        )