I was confident that I am good at DAX, but sadly I cannot seem to solve this easy problem. I am trying to replicate the calculations shown in the two blocks in the Excel file attached.
The task is simple, I want to calculate MAPE value, and then average it up to a specific level. Sadly I cannot seem to get it to work. I tried using AverageX together with groupby, summarize - nothing worked. Please help .(.xlsx (132.9 KB)
Hello, @Roboboboberts !
Please, find in attached file.
What i did:
on the row level of “Region” (when “Region” has value in the table) add classical formula for average:
sum of the volumes in the table column divide number of rows in that column.
using summarize and sumx:
New measure called “MAPE 1” Please help .(.xlsx (143.8 KB)
=IF(
HASONEVALUE(Table1[Region]);
– check, if on the row level of a table has “Region” values
– if “yes”, just calculates SUM of MAPE values and divide it on ROW Num
SUMX(
SUMMARIZE(‘Table1’;
Table1[Region];
‘Table1’[Market];
‘Table1’[Product];
“MAPE_0”;
‘Table1’[MAPE]);
[MAPE_0]) / COUNTROWS(‘Table1’);
– if “No”, returns automatically calculated MAPE value
‘Table1’[MAPE])
at the total level just auto calculated MAPE value.
Sometimes could face such cases - like described here: Fix Incorrect Totals
Hello @Roboboboberts, good to see that you are having progress with your inquiry. Did the response from @Oleg.Poddubnyy and @Rajesh help you solve your inquiry?
If it does, kindly mark as solution the answer that solved your query.
If not, how far did you get and what kind of help you need further?
