Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Date | Location | Actuals | Forecasts | Percent Error |
01/01/2018 | A | 113 | 118 | 4% |
01/01/2018 | B | 115 | 111 | 3% |
01/01/2018 | C | 117 | 114 | 3% |
02/01/2018 | A | 109 | 116 | 6% |
02/01/2018 | B | 117 | 114 | 3% |
02/01/2018 | C | 120 | 117 | 3% |
03/01/2018 | A | 111 | 100 | 10% |
03/01/2018 | B | 113 | 120 | 6% |
03/01/2018 | C | 117 | 104 | 11% |
04/01/2018 | A | 106 | 102 | 4% |
04/01/2018 | B | 102 | 109 | 7% |
04/01/2018 | C | 100 | 123 | 23% |
05/01/2018 | A | 116 | 105 | 9% |
05/01/2018 | B | 104 | 106 | 2% |
05/01/2018 | C | 108 | 116 | 7% |
06/01/2018 | A | 119 | 101 | 15% |
06/01/2018 | B | 103 | 110 | 7% |
06/01/2018 | C | 101 | 104 | 3% |
07/01/2018 | A | 106 | 113 | 7% |
07/01/2018 | B | 112 | 107 | 4% |
07/01/2018 | C | 118 | 125 | 6% |
08/01/2018 | A | 103 | 111 | 8% |
08/01/2018 | B | 112 | 113 | 1% |
08/01/2018 | C | 119 | 113 | 5% |
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:
Date | Actuals | Forecasts | Percent Error |
01/01/2018 | 345 | 343 | 0.6% |
02/01/2018 | 346 | 347 | 0.3% |
03/01/2018 | 341 | 324 | 5.0% |
04/01/2018 | 308 | 334 | 8.4% |
05/01/2018 | 328 | 327 | 0.3% |
06/01/2018 | 323 | 315 | 2.5% |
07/01/2018 | 336 | 345 | 2.7% |
08/01/2018 | 334 | 337 | 0.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?
Solved! Go to Solution.
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
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
This is exactly what I wanted to achieve! It works perfectlt! And it helped me simplify my data model.
Thank you very much,
Cath
User | Count |
---|---|
123 | |
111 | |
99 | |
60 | |
60 |
User | Count |
---|---|
138 | |
116 | |
102 | |
70 | |
57 |