Forum Discussion
All Function doest not work
daxHi Everyone. Im trying to create a measure that gives me the average price for ALL years in my data. But I have followed the instructions on how to use ALL but when I select a a year, my measu...
Hi Anonymous ，
Sorry for late repsonse. Please try to update the formula of measure All_Yr_NF as below:
All_Yr_NF = CALCULATE( AVERAGEX(VALUES(Winter_Contracts_Zema[Contract_Year]),[Final_Price_Norm]),ALL(Winter_Contracts_Zema))Best Regards
Rena
Hi,
Thanks so much!
This the sample data
The last 3 columns are calculated columns at Power Query Level
Norm_Price: PRice + Parameter Value
Percent Change: By python script day over day change
Norm_Change: By Python: Column 1 = 1 Column 2= Previous row 1 (Percent change * Previous row)
and my last measure to get price normalized is
Final_Price_Norm = sum(Winter_Contracts_Zema[Norm_Change]) - 1
Im just substracting 1 to everything.
| Date | Contract_Year | Contract_Type | Location | Price | Trade_Period | Contract_Key | Trade Day | Norm_Price | Percent_Change | Norm_Change |
| 39387 | 2008 | Winter | PG&E-Citygate | -0.13 | 2007/2008 | 2007/2008-FC 2008 | 1 | 1.87 | 0 | 1 |
| 39388 | 2008 | Winter | PG&E-Citygate | -0.16 | 2007/2008 | 2007/2008-FC 2008 | 2 | 1.84 | -0.01604 | 0.983957 |
| 39391 | 2008 | Winter | PG&E-Citygate | -0.135 | 2007/2008 | 2007/2008-FC 2008 | 3 | 1.865 | 0.013587 | 0.997326 |
| 39392 | 2008 | Winter | PG&E-Citygate | -0.12 | 2007/2008 | 2007/2008-FC 2008 | 4 | 1.88 | 0.008043 | 1.005348 |
| 39393 | 2008 | Winter | PG&E-Citygate | -0.11 | 2007/2008 | 2007/2008-FC 2008 | 5 | 1.89 | 0.005319 | 1.010695 |
| 39394 | 2008 | Winter | PG&E-Citygate | -0.12 | 2007/2008 | 2007/2008-FC 2008 | 6 | 1.88 | -0.00529 | 1.005348 |
| 39395 | 2008 | Winter | PG&E-Citygate | -0.12 | 2007/2008 | 2007/2008-FC 2008 | 7 | 1.88 | 0 | 1.005348 |
| 39398 | 2008 | Winter | PG&E-Citygate | -0.13 | 2007/2008 | 2007/2008-FC 2008 | 8 | 1.87 | -0.00532 | 1 |
| 39399 | 2008 | Winter | PG&E-Citygate | -0.14 | 2007/2008 | 2007/2008-FC 2008 | 9 | 1.86 | -0.00535 | 0.994652 |
| 39400 | 2008 | Winter | PG&E-Citygate | -0.14 | 2007/2008 | 2007/2008-FC 2008 | 10 | 1.86 | 0 | 0.994652 |
| 39401 | 2008 | Winter | PG&E-Citygate | -0.16 | 2007/2008 | 2007/2008-FC 2008 | 11 | 1.84 | -0.01075 | 0.983957 |
| 39402 | 2008 | Winter | PG&E-Citygate | -0.16 | 2007/2008 | 2007/2008-FC 2008 | 12 | 1.84 | 0 | 0.983957 |
| 39405 | 2008 | Winter | PG&E-Citygate | -0.17 | 2007/2008 | 2007/2008-FC 2008 | 13 | 1.83 | -0.00543 | 0.97861 |
| 39406 | 2008 | Winter | PG&E-Citygate | -0.15 | 2007/2008 | 2007/2008-FC 2008 | 14 | 1.85 | 0.010929 | 0.989305 |
| 39407 | 2008 | Winter | PG&E-Citygate | -0.15 | 2007/2008 | 2007/2008-FC 2008 | 15 | 1.85 | 0 | 0.989305 |
| 39409 | 2008 | Winter | PG&E-Citygate | -0.15 | 2007/2008 | 2007/2008-FC 2008 | 16 | 1.85 | 0 | 0.989305 |
| 39412 | 2008 | Winter | PG&E-Citygate | -0.145 | 2007/2008 | 2007/2008-FC 2008 | 17 | 1.855 | 0.002703 | 0.991979 |
| 39413 | 2008 | Winter | PG&E-Citygate | -0.135 | 2007/2008 | 2007/2008-FC 2008 | 18 | 1.865 | 0.005391 | 0.997326 |
| 39414 | 2008 | Winter | PG&E-Citygate | -0.115 | 2007/2008 | 2007/2008-FC 2008 | 19 | 1.885 | 0.010724 | 1.008021 |
Hi Anonymous ，
Sorry for late repsonse. Please try to update the formula of measure All_Yr_NF as below:
All_Yr_NF = CALCULATE( AVERAGEX(VALUES(Winter_Contracts_Zema[Contract_Year]),[Final_Price_Norm]),ALL(Winter_Contracts_Zema)) |
Best Regards
Rena