Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cathG
Regular Visitor

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 days by Location but I also want to be able to find the number of days for all Location combined (i.e. summing the Actuals, summing the Forecasts and calculating the errors.

 

So far, the only way I have found to achieve the task is by adding a calculated column that calculate the percent error and using that field in my DAX measure NoDays.  

 

NoDays= CALCULATE(COUNT(Table1[Percent Error]), FILTER(Table1, Table1[Percent Error]>0.05 ))

My table is as follow:

 

DateLocationActualsForecastsPercent Error
01/01/2018A1131184%
01/01/2018B1151113%
01/01/2018C1171143%
02/01/2018A1091166%
02/01/2018B1171143%
02/01/2018C1201173%
03/01/2018A11110010%
03/01/2018B1131206%
03/01/2018C11710411%
04/01/2018A1061024%
04/01/2018B1021097%
04/01/2018C10012323%
05/01/2018A1161059%
05/01/2018B1041062%
05/01/2018C1081167%
06/01/2018A11910115%
06/01/2018B1031107%
06/01/2018C1011043%
07/01/2018A1061137%
07/01/2018B1121074%
07/01/2018C1181256%
08/01/2018A1031118%
08/01/2018B1121131%
08/01/2018C1191135%

 

This works well when I select my different Locations, however, I am not able to count on a daily aggregated level the number of days the error is more than 5%. 

 

Briefly, when I select locations A, B and C in my slicer, I would like the underlying table to looks like this one below so I can count the number of days the Percent Error is more than 5% for all my locations combined.

 

On a daily aggregation:

DateActualsForecastsPercent Error
01/01/20183453430.6%
02/01/20183463470.3%
03/01/20183413245.0%
04/01/20183083348.4%
05/01/20183283270.3%
06/01/20183233152.5%
07/01/20183363452.7%
08/01/20183343370.9%

 

Basically I want a table that react and aggregate based on the selection of the filters and then count the number of days it fits my criteria in my NoDays Measure.

 

I have tried to used Percent Error as a measure for the filtering in the NoDays Measure  but that does not work. Then I thought that maybe there is a way to use a dynamic table in the Measure. Something that will allow me to summarize my table within the NoDays Measure and then proceed with the count on the resulted table... but I couldn't make this work..

 

Does anyone has an idea how to do that?

 

 

 

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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

1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.