Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm trying to develop a PowerBI Dashboard to measure the forecast accuracy:
My datastructure is like this in one Table (Several Countries, several Articles and Several Months)
Article | Country | Month_sales | Month_Forecast | Month_no | |
1 | DE | 100 | 200 | 10.2019 | |
1 | DE | 200 | 400 | 11.2019 | |
1 | DE | 600 | 400 | 12.2019 | |
2 | DE | 50 | 100 | 10.2019 | |
2 | DE | 150 | 200 | 11.2019 | |
2 | DE | 300 | 600 | 12.2019 | |
1 | JR | 100 | 200 | 10.2019 |
If I want to measure the different Forecast KPI it works perfectly for single months:
BIAS (The Difference between Sales and Plan). The measure is: SUM(Table[Month_Forecast]) - Sum(Table[Month_Sales])
MAD(The Sum of the absolute difference of each article line). The measure is = SUMX(Table;ABS(Table[Month_Forecast] - Table[Month_Sales]))
If I want to measure the same KPI for several months (in this case the months 10.2019 - 12.2019) I don't know how to measure the MAD:
BIAS ==> The same DAX expression like for single months works perfectly
MAD==> If I am using the same expressions like for single months it shows me a MAD of 900 pcs for country DE (The measue calculates the sum of the ABS difference per each article and month!)
What the measure should do is to calculate the absolute difference per article for the whole selected time range (10.2019 - 12.2019): An Example:
==> So the MAD for the country DE in the time range 10.2019 - 12.2019 should be 500 pcs, but I don't know how to generate the Measure.
I hope someone can help.
Many Thanks in advance,
Mario
Hi @Mario_Z ,
You could edit your measure like the following DAX:
MAD =
ABS ( SUMX ( ALLSELECTED ( Table ), Table[Month_sales] - Table[Month_Forecast] ) )
Hi,
I think your suggestion solves the problem in the example, but the dax expressions do the same like the BIAS. In my example BIAS and MAD have the same number, so a bad selected example.
Maybe a better example with the data below:
MAD should be: 1000 =(MAD Article 1 (100) + MAD article 2 (400) + MAD article 3(500))
MAD in time range for article 1 = 1000 - 900 = 100
MAD in time range for article 2 = 900 - 500 = 400
MAD in time range for article 3 = 1500 - 2000 = 500
Article | Country | Month_sales | Month_forecast | Month_no |
1 | DE | 100 | 200 | Okt. 19 |
1 | DE | 200 | 400 | Nov. 19 |
1 | DE | 600 | 400 | Dez. 19 |
2 | DE | 50 | 100 | Okt. 19 |
2 | DE | 150 | 200 | Nov. 19 |
2 | DE | 300 | 600 | Dez. 19 |
3 | DE | 800 | 500 | Okt. 19 |
3 | DE | 200 | 800 | Nov. 19 |
3 | DE | 1000 | 200 | Dez. 19 |
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |