Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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


H2SO4 kg/d =
CALCULATE([H2SO4 mg/l]*[APL Bleed m3/day])
 
Avg_10_H2SO4_(kg/d) =
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 ( 1Remove_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.

 
Minakami_1-1655827220531.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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?

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

I changed the:

H2SO4 kg/d =
CALCULATE([H2SO4 mg/l]*[APL Bleed m3/day])

INTO

H2SO4 kg/d =
SUMX(ImportOfLIMS, ImportOfLIMS[H2SO4 mg/l]*[APL Bleed m3/day])

Output is now the same as the avg= 2595. how can that be if on 21st june : [H2SO4 mg/l] = 82,30 AND BLEED IS 30,03
82.30*30  IS 2471...
Anonymous
Not applicable

@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.

Anonymous
Not applicable

Thanks for you comment. I followed your advice and the output for june 21st = minn 2595 and maxx also 2595

Minakami_0-1655840139912.png
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
Not applicable

@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?

Anonymous
Not applicable

The correct result is 2471. 

 

after applying the debug code I get:

Minakami_0-1655883585568.png

   

your suggestion to edit out the sumx from the ADDCOLUMNS did the trick!

VAR AllDates_H2SO4 =
        ADDCOLUMNS (
            AllDatesPrior,
            //"@H2SO4", CALCULATE ( SUMX ( ImportOfLIMS, 'ImportOfLIMS'[H2SO4 kg/d] ) )
            "@H2SO4", [H2SO4 kg/d]
        )

after this I do get the right result: 2471.

 

I still don't really comprehend how that would matter and where the difference comes from. I feel like if I don't fully understand this i'l keep on making this same mistake in the future.

 

you said: "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."

I didn't know that ADDCOLUMNS automatically goes over every row just like SUMX. but does that explain the difference in value? if so, how exactly? 

 

Anonymous
Not applicable

@Anonymous Glad it worked! 

I'm not sure why the CALCULATE SUMX didn't work, either. The CALCULATE function is used to change the context of the expression, but in this case there were no changes to filters applied. The SUMX also is an iterator and changes the context, so a suspect the combination of ADDCOLUMNS, CALCULATE and SUMX together has ended up with a context that is different to expected. 

To delve further you could add more debug code to examine what the CALCULATE-SUMX produces. For example, change it to each of these to check out what data it is producing.

CALCULATE ( MINX( ImportOfLIMS, 'ImportOfLIMS'[Date] ) )
CALCULATE ( MAXX( ImportOfLIMS, 'ImportOfLIMS'[Date] ) )
CALCULATE ( COUNTROWS( ImportOfLIMS))
CALCULATE ( MINX( ImportOfLIMS, 'Calendar'[Date] ) )
CALCULATE ( MAXX( ImportOfLIMS, 'Calendar'[Date] ) )

 

Yes, ADDCOLUMNS is an iterator function.

ADDCOLUMNS – DAX Guide

 

Anonymous
Not applicable

I will try and further debug this one soon. at leastnow  I can continue building 😃 

 

for now, thanks a lot for your help. appreciate it very much! 

 

Anonymous
Not applicable

@Anonymous happy to help. Found this article which looks like it explains the issues in this case.

Context Transition and Expanded Tables - SQLBI

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.