Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am getting an Error when I am trying to use the standard POWER function cacluation in Power Pivot (DAX) environment.
Specifically I want to create a Calculated Column 'Impact' based on 'simple' Arithmetic calculation of two columns
'Price Index' ^ 'Coefficient'.
'Coefficient' is a fixed value that is unique to each Product. 'Price Index' is a Calculated Column where each Product's price in 2024 is indexed to the Product's price in 2023. ie for Product A June '24 'Price Index' = 'June '24 Price'/'June '23 Price'
Intuitively I want every row where a 'Price Index' exists to have an 'Impact' value ie
Product A - June '24 1.1^0.31 = 1.030
Product A - July '24 1.105^.31 = 1.032
Product B - June '24 1.140 ^ .20 = 1.027
repeat
I am writing the formula in Dax as [Price Index]^[Coefficient] which results in an 'Error'. I am ok with an Error or missing values in rows where 'Price Index' does not exit. I have double checked using ISNUMBER function that both 'Price Index' and 'Coefficinent' are formatted as a decimal number. I have tried re-writing the formula as POWER ([Price Index], [Coefficient]) with similar results.
I am struggling to find documentation on the 'POWER' function properties to help me resolve this error. Any tips? Thank you
Product | Period | Price | Price Index | Coefficient | Impact |
Product A | June '23 | 100 | 0.31 | ||
Product A | July '23 | 95 | 0.31 | ||
Product A | August '23 | 90 | 0.31 | ||
Product A | June '24 | 110 | 1.100 | 0.31 | 1.030 |
Product A | July '24 | 105 | 1.105 | 0.31 | 1.032 |
Product A | August '24 | 99 | 1.100 | 0.31 | 1.030 |
Product B | June '23 | 50 | 0.2 | ||
Product B | July '23 | 52 | 0.2 | ||
Product B | August '23 | 53 | 0.2 | ||
Product B | June '24 | 57 | 1.140 | 0.2 | 1.027 |
Product B | July '24 | 56 | 1.077 | 0.2 | 1.015 |
Product B | August '24 | 52 | 0.981 | 0.2 | 0.996 |
Solved! Go to Solution.
you can replace COALESCE with IF(ISBLANK([Price Index]),1,[Price Index])
thank you so much for your reply! This did not solve the issue, but your comment prompted me to review the original problem and indentify the source of the error. I shared an incomplete Table. True data looked like this, with the ∞ value in rows where 'Price Year Ago' was 0. It seems like DAX could not compute the POWER(∞, [Coefficient]) for certain rows and stopped calculating for all other rows as well.
I re-wrote the 'Price Index' calculated column with a IFERROR([Price]/[Price YA],1) so now every row is populated with either a 1 or 'Calculated Price Index'.
Thank you again for your help.
Product | Period | Price | Price Index | Coefficient | Impact |
Product A | June '23 | 100 | ∞ | 0.31 | |
Product A | July '23 | 95 | ∞ | 0.31 | |
Product A | August '23 | 90 | ∞ | 0.31 | |
Product A | June '24 | 110 | 1.1 | 0.31 | 1.03 |
Product A | July '24 | 105 | 1.105 | 0.31 | 1.032 |
Product A | August '24 | 99 | 1.1 | 0.31 | 1.03 |
Product B | June '23 | 50 | ∞ | 0.2 | |
Product B | July '23 | 52 | ∞ | 0.2 | |
Product B | August '23 | 53 | ∞ | 0.2 | |
Product B | June '24 | 57 | 1.14 | 0.2 | 1.027 |
Product B | July '24 | 56 | 1.077 | 0.2 | 1.015 |
Product B | August '24 | 52 | 0.981 | 0.2 | 0.996 |
Not seeing the issue here?
Thank you so much for your quick reply. I am not seeing the COALESCE function in Power Pivot. Is your example executed in Power Pivot or another tool. Sorry for the beginner questions. I am just getting started in this field.
you can replace COALESCE with IF(ISBLANK([Price Index]),1,[Price Index])
thank you so much for your reply! This did not solve the issue, but your comment prompted me to review the original problem and indentify the source of the error. I shared an incomplete Table. True data looked like this, with the ∞ value in rows where 'Price Year Ago' was 0. It seems like DAX could not compute the POWER(∞, [Coefficient]) for certain rows and stopped calculating for all other rows as well.
I re-wrote the 'Price Index' calculated column with a IFERROR([Price]/[Price YA],1) so now every row is populated with either a 1 or 'Calculated Price Index'.
Thank you again for your help.
Product | Period | Price | Price Index | Coefficient | Impact |
Product A | June '23 | 100 | ∞ | 0.31 | |
Product A | July '23 | 95 | ∞ | 0.31 | |
Product A | August '23 | 90 | ∞ | 0.31 | |
Product A | June '24 | 110 | 1.1 | 0.31 | 1.03 |
Product A | July '24 | 105 | 1.105 | 0.31 | 1.032 |
Product A | August '24 | 99 | 1.1 | 0.31 | 1.03 |
Product B | June '23 | 50 | ∞ | 0.2 | |
Product B | July '23 | 52 | ∞ | 0.2 | |
Product B | August '23 | 53 | ∞ | 0.2 | |
Product B | June '24 | 57 | 1.14 | 0.2 | 1.027 |
Product B | July '24 | 56 | 1.077 | 0.2 | 1.015 |
Product B | August '24 | 52 | 0.981 | 0.2 | 0.996 |
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
37 | |
21 |