Forum Discussion
Calculated average value isnt correct
H2SO4 mg/l =
VAR H2SO4_apl =
CALCULATE (
SUMX ( ImportOfLIMS, ImportOfLIMS[H2SO4] ),
ImportOfLIMS[SAMPLE_GROUP_NAME] = "Acid Plant"
)
VAR isblankH2SO4 =
IF (
ISBLANK ( H2SO4_apl ),
SUMX ( WasteWaterapl_ambernet, WasteWaterAPL_ambernet[H2SO4 g/l Ambernet] ),
( H2SO4_apl )
)
VAR dgTOg =
DIVIDE ( ( isblankH2SO4 ), 10 )
RETURN
dgTOg
VAR CurrentDate =
MAX ( 'Calender'[Date] )
VAR AllDatesPrior =
FILTER ( ALL ( 'Calender' ), 'Calender'[Date] <= CurrentDate )
VAR AllDates_H2SO4 =
ADDCOLUMNS (
AllDatesPrior,
"@H2SO4", CALCULATE ( SUMX ( ImportOfLIMS, 'ImportOfLIMS'[H2SO4 kg/d] ) )
)
VAR Remove_BlankH2SO4 =
FILTER ( AllDates_H2SO4, [@H2SO4] <> BLANK () )
VAR MostRecentDates =
TOPN ( 1, Remove_BlankH2SO4, 'Calender'[Date], DESC )
VAR Result =
AVERAGEX ( MostRecentDates, [@H2SO4] )
VAR Noblanks =
IF ( ISBLANK ( [H2SO4 kg/d] ), BLANK (), Result )
RETURN
Noblanks
I edited Topn into = 1 to make things clearer. as you can see below, even though I calculated a 1 day average of the kg/d it still didnt give me the same output. if anyone could help out that would be great.
Anonymous , that's interesting. Try adding some more debug code. I'm interested to see if the two methods you use to refer to [H2SO4 kg/d] give the same result.
VAR H2SO4measure = [H2SO4 kg/d] VAR H2SO4column = CALCULATE ( SUMX ( ImportOfLIMS, 'ImportOfLIMS'[H2SO4 kg/d] ) ) VAR Bleed = [APL Bleed m3/day] VAR H2SO4mg = [H2SO4 mg/l] RETURN FORMAT(MINX(MostRecentDates, [Date]), "YYYYMMDD") & " ; " & FORMAT(MAXX(MostRecentDates, [Date]), "YYYYMMDD") & " ; " & MINX(MostRecentDates, [@H2SO4]) & " ; " & MAXX(MostRecentDates, [@H2SO4]) & " ; " & FORMAT(Result, "0.00") & " ; " & FORMAT(H2SO4measure, "0.00") & " ; " & FORMAT(Bleed, "0.00") & " ; " & FORMAT(H2SO4mg, "0.00")If 'ImportOfLIMS'[H2SO4 kg/d] is actually a measure and not a column, then I believe you can change the [Avg_10_H2SO4_(kg/d)] to refer to it like this below. ADDCOLUMNS is an iterator and will calculate the expression for "@H2SO4" for every row in the AllDatesPrior table variable, so there is no need for a SUMX or CALCULATE.
VAR AllDates_H2SO4 = ADDCOLUMNS ( AllDatesPrior, //"@H2SO4", CALCULATE ( SUMX ( ImportOfLIMS, 'ImportOfLIMS'[H2SO4 kg/d] ) ) "@H2SO4", [H2SO4 kg/d] )Incidentally, from the raw data, what actually is the correct result for 21 Jun?
8 Replies
Anonymous , it looks like your [H2SO4 kg/d] and [Avg_10_H2SO4_(kg/d)] measures in the column above are calculated slightly differently.
The [H2SO4 kg/d] measure is using data from [H2SO4 mg/l] * [APL Bleed m3/day],
but the [Avg_10_H2SO4_(kg/d)] measure is averaging the SUMX of the column 'ImportOfLIMS'[H2SO4 kg/d].
Are you sure this is exactly the same data?
Is 'ImportOfLIMS'[H2SO4 kg/d] a column in a source table, or it is the measure defined in your post?
To help look at what is being produced by the measure more closely, you could try replaceing the "RETURN NoBlanks" in your measure with this:
RETURN FORMAT(MINX(MostRecentDates, [Date]), "YYYYMMDD") & " ; " & FORMAT(MAXX(MostRecentDates, [Date]), "YYYYMMDD") & " ; " & MINX(MostRecentDates, [@H2SO4]) & " ; " & MAXX(MostRecentDates, [@H2SO4]) & " ; " & FORMAT(Result, "0.00")and this will give you some "debug" output to analyse.
Thanks for you comment. I followed your advice and the output for june 21st = minn 2595 and maxx also 2595
also you said that "the [Avg_10_H2SO4_(kg/d)] measure is averaging the SUMX of the column 'ImportOfLIMS'[H2SO4 kg/d]."am I averaging the column or the row by row in the column? My intention was the 2nd.
Anonymous , that's interesting. Try adding some more debug code. I'm interested to see if the two methods you use to refer to [H2SO4 kg/d] give the same result.
VAR H2SO4measure = [H2SO4 kg/d] VAR H2SO4column = CALCULATE ( SUMX ( ImportOfLIMS, 'ImportOfLIMS'[H2SO4 kg/d] ) ) VAR Bleed = [APL Bleed m3/day] VAR H2SO4mg = [H2SO4 mg/l] RETURN FORMAT(MINX(MostRecentDates, [Date]), "YYYYMMDD") & " ; " & FORMAT(MAXX(MostRecentDates, [Date]), "YYYYMMDD") & " ; " & MINX(MostRecentDates, [@H2SO4]) & " ; " & MAXX(MostRecentDates, [@H2SO4]) & " ; " & FORMAT(Result, "0.00") & " ; " & FORMAT(H2SO4measure, "0.00") & " ; " & FORMAT(Bleed, "0.00") & " ; " & FORMAT(H2SO4mg, "0.00")If 'ImportOfLIMS'[H2SO4 kg/d] is actually a measure and not a column, then I believe you can change the [Avg_10_H2SO4_(kg/d)] to refer to it like this below. ADDCOLUMNS is an iterator and will calculate the expression for "@H2SO4" for every row in the AllDatesPrior table variable, so there is no need for a SUMX or CALCULATE.
VAR AllDates_H2SO4 = ADDCOLUMNS ( AllDatesPrior, //"@H2SO4", CALCULATE ( SUMX ( ImportOfLIMS, 'ImportOfLIMS'[H2SO4 kg/d] ) ) "@H2SO4", [H2SO4 kg/d] )Incidentally, from the raw data, what actually is the correct result for 21 Jun?