Forum Discussion
Issues with calculating AVERAGEX with and without using ALLEXCEPT
Hello All,
I want to calculate Average percent change over a grouped category but I keep getting a blank column. I have tried with ALLEXCEPT, and I have tried with using SUMMARIZE with the same result. I am not sure where to go from here as I cant get any hints from errors as to what is going on. I have doctored and shortened my data here a bit but here is the data:
BuildPhase | MetricDescription | CntVariable | Month_YY - Year | Month_YY - Month | Cnt | PreviousMonthCnt | CntVariance | PctChange | AvgPctChange |
1 | Count of M&Ms sold from store 1 | M&Ms | 2022 | November | 2218267 | 2218267 | |||
1 | Count of M&Ms sold from store 1 | M&Ms | 2022 | December | 2400358 | 2218267 | 182091 | 8.21% | |
1 | Count of M&Ms sold from store 1 | M&Ms | 2023 | January | 2408373 | 2400358 | 8015 | 0.33% | |
1 | Count of M&Ms sold from store 1 | M&Ms | 2023 | February | 2419882 | 2408373 | 11509 | 0.48% | |
1 | Count of M&Ms sold from store 1 | M&Ms | 2023 | March | 2457902 | 2419882 | 38020 | 1.57% | |
1 | Count of M&Ms sold from store 2 | M&Ms | 2022 | November | 3502 | 3502 | |||
1 | Count of M&Ms sold from store 2 | M&Ms | 2022 | December | 2975 | 3502 | -527 | -15.05% | |
1 | Count of M&Ms sold from store 2 | M&Ms | 2023 | January | 2610 | 2975 | -365 | -12.27% | |
1 | Count of M&Ms sold from store 2 | M&Ms | 2023 | February | 4317 | 2610 | 1707 | 65.40% | |
1 | Count of M&Ms sold from store 2 | M&Ms | 2023 | March | 4570 | 4317 | 253 | 5.86% | |
1 | Count of Twix from sold from store 1 | Twix | 2022 | November | 18237 | 18237 | |||
1 | Count of Twix from sold from store 1 | Twix | 2022 | December | 31011 | 18237 | 12774 | 70.04% | |
1 | Count of Twix from sold from store 1 | Twix | 2023 | January | 8014 | 31011 | -22997 | -74.16% | |
1 | Count of Twix from sold from store 1 | Twix | 2023 | February | 11509 | 8014 | 3495 | 43.61% | |
1 | Count of Twix from sold from store 1 | Twix | 2023 | March | 9990 | 11509 | -1519 | -13.20% | |
1 | Count of Snickers sold from store 3 | Snickers | 2022 | November | 50507 | 50507 | |||
1 | Count of Snickers sold from store 3 | Snickers | 2022 | December | 76717 | 50507 | 26210 | 51.89% | |
1 | Count of Snickers sold from store 3 | Snickers | 2023 | January | 21253 | 76717 | -55464 | -72.30% | |
1 | Count of Snickers sold from store 3 | Snickers | 2023 | February | 28647 | 21253 | 7394 | 34.79% | |
1 | Count of Snickers sold from store 3 | Snickers | 2023 | March | 22700 | 28647 | -5947 | -20.76% | |
1 | Count of Skittles sold from store 3 | Skittles | 2022 | November | 2373063 | 2373063 | |||
1 | Count of Skittles sold from store 3 | Skittles | 2022 | December | 2487148 | 2373063 | 114085 | 4.81% | |
1 | Count of Skittles sold from store 3 | Skittles | 2023 | January | 2625641 | 2487148 | 138493 | 5.57% | |
1 | Count of Skittles sold from store 3 | Skittles | 2023 | February | 2414079 | 2625641 | -211562 | -8.06% | |
1 | Count of Skittles sold from store 3 | Skittles | 2023 | March | 2266821 | 2414079 | -147258 | -6.10% |
I would like the grouping by BuildPhase, MetricDescription, CntVariable. PreviousMonthCnt, CntVariance, and, PctChange are all measures I have made.
I have tried this DAX formula:
2 Replies
BmejiaSuper User
Your calculation with Allexempt is working, I am not sure why you are getting blanks.
That is bizzare. I wonder if its a datatype issue? What were your column formats when you pasted the above into pbix?
