Forum Discussion

cathG's avatar
cathG
Regular Visitor
8 years ago
Solved

using a measure for filtering when count rows

I have a table with Forecast and Actual values by day and by Location. I want to count the number of days the percent error is above 5%. By using a slicer for Location, I want to find the numbers of ...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi cathG,

     

    Please try below measures:

    Percent Error measure =
ABS ( SUM ( Table1[Forecasts] ) - SUM ( Table1[Actuals] ) )
    / SUM ( Table1[Actuals] )

NoDays2 =
VAR temptb =
    SUMMARIZE (
        Table1,
        Table1[Date],
        "Daily Actual", SUM ( Table1[Actuals] ),
        "Daily Forecast", SUM ( Table1[Forecasts] ),
        "Percent Error Col", ABS ( SUM ( Table1[Actuals] ) - SUM ( Table1[Forecasts] ) )
            / SUM ( Table1[Actuals] )
    )
RETURN
    COUNTX ( FILTER ( temptb, [Percent Error Col] > 0.05 ), [Date] )

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi cathG,

 

Please try below measures:

Percent Error measure =
ABS ( SUM ( Table1[Forecasts] ) - SUM ( Table1[Actuals] ) )
    / SUM ( Table1[Actuals] )

NoDays2 =
VAR temptb =
    SUMMARIZE (
        Table1,
        Table1[Date],
        "Daily Actual", SUM ( Table1[Actuals] ),
        "Daily Forecast", SUM ( Table1[Forecasts] ),
        "Percent Error Col", ABS ( SUM ( Table1[Actuals] ) - SUM ( Table1[Forecasts] ) )
            / SUM ( Table1[Actuals] )
    )
RETURN
    COUNTX ( FILTER ( temptb, [Percent Error Col] > 0.05 ), [Date] )

 

Best regards,

Yuliana Gu

cathG's avatar
cathG
Regular Visitor
8 years ago

Hi v-yulgu-msft

 

This is exactly what I wanted to achieve! It works perfectlt! And it helped me simplify my data model.

Thank you very much,

Cath