Average Values with multiple criteria

[croine]

Hi all,

I have just started learning Power Bi and I got stuck almost at the begging. Not sure how complicated this is, but I can`t seem to get to an answer by myself. 

I need to infill the average of a column based on the last 7 values that are not blank and if this can`t be done then base the average on the whole column.

Thank you very much in advance! 

[vojtechsima]

@croine 
Hi, could you please provide sample data for your request?

[croine]

Hi, 

Sure, sorry I forgot to add that. I need to apply the averages for the [Fixed Hour] and [Daily Average] when point counts dont meet a criteria previously set up.

[vojtechsima]

@croine 
No problem, could you please paste it here as a Table, so I can copy it and come with a tailor-made solution? 
thanks

[croine]

Hope I am doing this right 

CPPs	Date	Fixed hour	Daily Average	Daily pressure point count	Fixed hour point count
CPP_9983_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9984_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9985_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9986_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9987_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9988_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9989_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9990_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9992_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9993_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9994_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9995_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9996_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9997_PRESSURE_R	01/04/2019 00:00			0	0
CPP_9998_PRESSURE_R	01/04/2019 00:00			0	0
CPP_293_PRESSURE_R	01/04/2019 00:00	0.987411176		0	0
CPP_6999_PRESSURE_R	01/04/2019 00:00	0.995377362		0	0
CPP_335_PRESSURE_R	01/04/2019 00:00	1.200000048	1.237500012	96	4
CPP_6213_PRESSURE_R	01/04/2019 00:00	1.25	22.8260417	96	4
CPP_6798_PRESSURE_R	01/04/2019 00:00	4.01321194		0	0
CPP_2385_PRESSURE_R	01/04/2019 00:00	4.454083887		0	0
CPP_5296_PRESSURE_R	01/04/2019 00:00	7.110839356		0	0
CPP_7305_PRESSURE_R	01/04/2019 00:00	7.175306121		0	0
CPP_1395_PRESSURE_R	01/04/2019 00:00	11.36093381		0	0
CPP_2394_PRESSURE_R	01/04/2019 00:00	11.46102538		0	0
CPP_344_PRESSURE_R	01/04/2019 00:00	13	12.24479165	96	4
CPP_71_PRESSURE_R	01/04/2019 00:00	14	13.72812501	96	4
CPP_7699_PRESSURE_R	01/04/2019 00:00	15.68333324	22.07604166	96	4
CPP_7197_PRESSURE_R	01/04/2019 00:00	16.36666616	17.08541661	96	4
CPP_33_PRESSURE_R	01/04/2019 00:00	16.4666667	12.13854173	96	4
CPP_5393_PRESSURE_R	01/04/2019 00:00	17.23829804		0	0
CPP_6797_PRESSURE_R	01/04/2019 00:00	17.53333346	19.00729179	96	4
CPP_6611_PRESSURE_R	01/04/2019 00:00	17.98333359	17.01874997	96	4
CPP_5114_PRESSURE_R	01/04/2019 00:00	18.08333365	15.80625001	96	4
CPP_4508_PRESSURE_R	01/04/2019 00:00	18.38333257	17.81458324	96	4
CPP_2698_PRESSURE_R	01/04/2019 00:00	18.44015744		0	0
CPP_0288_PRESSURE_R	01/04/2019 00:00	18.53333346	19.79374989	96	4
CPP_1398_PRESSURE_R	01/04/2019 00:00	18.60000038	17.42395832	96	4
CPP_7078_PRESSURE_R	01/04/2019 00:00	18.61666711	18.56875012	96	4
CPP_5298_PRESSURE_R	01/04/2019 00:00	18.6833334	19.79062496	96	4
CPP_7055_PRESSURE_R	01/04/2019 00:00	18.93333308	17.82708327	96	4
CPP_338_PRESSURE_R	01/04/2019 00:00	18.9333334	19.31770839	96	4
CPP_317_PRESSURE_R	01/04/2019 00:00	19	17.75833324	96	4
CPP_191_PRESSURE_R	01/04/2019 00:00	19.06728143		0	0
CPP_327_PRESSURE_R	01/04/2019 00:00	19.20000076	19.09687535	96	4
CPP_4188_PRESSURE_R	01/04/2019 00:00	19.28333346	21.37291666	96	4
CPP_5299_PRESSURE_R	01/04/2019 00:00	19.33333302	19.73020832	96	4
CPP_346_PRESSURE_R	01/04/2019 00:00	19.44999981	19.05000013	96	4
CPP_347_PRESSURE_R	01/04/2019 00:00	19.46666654	19.36979151	96	4
CPP_73_PRESSURE_R	01/04/2019 00:00	19.48333327	20.53684219	95	4
CPP_6297_PRESSURE_R	01/04/2019 00:00	19.5	21.16947361	95	4
CPP_2899_PRESSURE_R	01/04/2019 00:00	19.54999987	18.62916658	96	4
CPP_6159_PRESSURE_R	01/04/2019 00:00	19.60000038	20.69270826	96	4
CPP_2053_PRESSURE_R	01/04/2019 00:00	19.63368099	20.10000038	1	0
CPP_2885_PRESSURE_R	01/04/2019 00:00	19.70000013	19.76041661	96	4
CPP_591_PRESSURE_R	01/04/2019 00:00	19.84999975	24.22604163	96	4
CPP_5113_PRESSURE_R	01/04/2019 00:00	19.85633098		0	0
CPP_64_PRESSURE_R	01/04/2019 00:00	19.86666616	18.82291664	96	4
CPP_353_PRESSURE_R	01/04/2019 00:00	19.91666698	18.89270829	96	4
CPP_2993_PRESSURE_R	01/04/2019 00:00	20.13333352	21.2208333	96	4
CPP_599_PRESSURE_R	01/04/2019 00:00	20.19999949	23.09062499	96	4
CPP_3391_PRESSURE_R	01/04/2019 00:00	20.25	20.20937494	96	4
CPP_3589_PRESSURE_R	01/04/2019 00:00	20.28333314	20.93333334	96	4
CPP_322_PRESSURE_R	01/04/2019 00:00	20.43333308	19.92916663	96	4
CPP_2803_PRESSURE_R	01/04/2019 00:00	20.58333302	20.53854165	96	4
CPP_492_PRESSURE_R	01/04/2019 00:00	20.63333352	26.60312506	96	4
CPP_342_PRESSURE_R	01/04/2019 00:00	20.73333327	21.04583339	96	4
CPP_692_PRESSURE_R	01/04/2019 00:00	20.78333282	20.79999989	96	4
CPP_7054_PRESSURE_R	01/04/2019 00:00	20.79999924	20.29999977	96	4
CPP_501_PRESSURE_R	01/04/2019 00:00	20.9333334	21.74687505	96	4
CPP_28_PRESSURE_R	01/04/2019 00:00	20.93571162		0	0

[vojtechsima]

@croine 
Thank you for your input,
Can you please again clarify what you need? I am not sure I get your points. Which Columns to fill? 7 latest values in what column and based on what criteria? 

[croine]

I need to infill Fixed Hour and Daily Average's missing data with available values in the columns. First by averaging the last 7 values above the blank and if those are not available, then infill with a column average.

Or I can create 2 new columns, which I was trying to do and failed.

[vojtechsima · Accepted solution]

Hi, @croine 
I got your result:
There are some things that could be optimized but if you need it to work in the first place, it should be all you need:
I started with creating some "Ranking" columns in order to evaluate 7 ABOVE rows, I used simply Index Column in Power Query and worked with it like that.
Then I added two columns to check, whether the column is empty or not (This could be optimized later)
Finally, two more columns that actually fill the missing info based on your criteria (you can't fill existing columns in DAX)

Here is the code:

DailyAverageBlankCheck = 
var BlankCheck = IF(ISBLANK('Table'[Daily Average]),0,1)

return BlankCheck

FixedHourBlankCheck = 
var BlankCheck = IF(ISBLANK('Table'[Fixed hour]),0,1)

return BlankCheck

DailyAverageFixed = 
var currentRank = 'Table'[Index]
var sevenRowsAbove = FILTER('Table', currentRank >= 'Table'[Index])
var TableForAverage = TOPN(8,sevenRowsAbove,'Table'[Index])
var Top7Check = CALCULATE(SUM('Table'[DailyAverageBlankCheck]),TOPN(7,sevenRowsAbove,'Table'[Index]))
var DailyAverage = CALCULATE(AVERAGEX('Table','Table'[Daily Average]),FILTER('Table','Table'[DailyAverageBlankCheck]=1))

var Result = SWITCH(TRUE(),
    NOT(ISBLANK('Table'[Daily Average])), 'Table'[Daily Average],
    'Table'[DailyAverageBlankCheck]=0 && Top7Check=6, AVERAGEX(TableForAverage,'Table'[Daily Average]),
    DailyAverage)

return Result

FixedHourFixed = 
var currentRank = 'Table'[Index]
var sevenRowsAbove = FILTER('Table', currentRank >= 'Table'[Index])
var TableForAverage = TOPN(8,sevenRowsAbove,'Table'[Index])
var Top7Check = CALCULATE(SUM('Table'[FixedHourBlankCheck]),TOPN(7,sevenRowsAbove,'Table'[Index]))
var HoursAverage = CALCULATE(AVERAGEX('Table','Table'[Fixed hour]),FILTER('Table','Table'[FixedHourBlankCheck]=1))

var Result = SWITCH(TRUE(),
    NOT(ISBLANK('Table'[Fixed hour])), 'Table'[Fixed hour],
    'Table'[FixedHourBlankCheck]=0 && Top7Check=6, AVERAGEX(TableForAverage,'Table'[Fixed hour]),
    HoursAverage)

return Result

 
Sample data screen:

[croine]

Thanks a lot! I`ve learned so many things from this solution only, much appreciated!