Incorrect calculation of sumx

[Anonymous]

Hello everyone,

I have a table with a column with the time and a column with the flow. I am trying to dinamically calculate the volume of selected periods as the area between each value ((Q2-Q1)/2 +Q1)*timestep. I imagine I should use something with sumx and come up with the formula below. When I disgregate in the different timesteps it calculate the values right, However if a use a summary table (by days for example) the value is completely different from what it should be. I obvuously am not getting how the context work but I wouldappreciate some help

At the bottom there is an example of wthe result I need. The Increment is what I want to get, the Volume column is what I get. The results are wrong when I look at the totals or start grouping by days or other periods of time.

Volume(m3) =

Var current_time = maxx(Sheet1,Sheet1[Time])

Var current_value = maxX(Sheet1,Sheet1[Flow (m3/h)])

Var Previous_time = maxx(filter(all(Sheet1[Time]),Sheet1[Time]<current_time),Sheet1[Time])

Var previous_value = calculate(max(Sheet1[Flow (m3/h)]),all(Sheet1[Time]),Sheet1[time]=Previous_time)

var Increment = (current_value-previous_value)/2+previous_value

Var volume = sumx(Sheet1,if(isblank(Previous_time),0,Increment))

RETURN

volume

[Anonymous · Accepted solution]

Apologies for coming back so late. The provided code did not work. In the end I made some reasearch and the problem was that the variables were defined outside the Addcolumns formulas, so it was not using that context. So I defined the variables inside the addcolumns so it would respect the rows context of the virtual table  when looking for the previous timestep (see code below)

Volume(m3) =
VAR Tbl =
    ADDCOLUMNS (
        SUMMARIZE ( sheet1, Sheet1[Time] ),
        "Flow",
            CALCULATE (
                VAR current_time =
                    CALCULATE ( MAX ( Sheet1[Time] ) )
                VAR current_value =
                    CALCULATE ( MAX ( Sheet1[Flow (m3/h)] ) )
                VAR Previous_time =
                    CALCULATE (
                        MAX ( Sheet1[Time] ),
                        FILTER ( ALL ( Sheet1[Time] ), Sheet1[Time] < current_time )
                    ) //Var previous_value = calculate(max(Sheet1[Flow (m3/h)]),all(Sheet1[Time]),Sheet1[time]=Previous_time)
                VAR previous_value =
                    CALCULATE (
                        MAX ( Sheet1[Flow (m3/h)] ),
                        FILTER ( ALL ( Sheet1 ), Sheet1[Time] = Previous_time )
                    )
                VAR Increment = ( current_value - previous_value ) / 2 + previous_value
                VAR Volume =
                    CALCULATE ( IF ( ISBLANK ( Previous_time ), 0, Increment ) )
                RETURN
                    Volume
            )
    )
RETURN
    SUMX ( Tbl, [Flow] )

[v-luwang-msft]

Hi @Anonymous ,

Try the below measure.If still not solved .Could you share your pbix file for me to test ,and remember to remove your confidential data.

measure1:

Previous_time =
Var current_time = maxx(Sheet1,Sheet1[Time])
Var current_value = maxX(Sheet1,Sheet1[Flow (m3/h)])
Var Previous_time = maxx(filter(all(Sheet1[Time]),Sheet1[Time]<current_time),Sheet1[Time])
return  Previous_time 

measure2:

Increment  =
Var current_time = maxx(Sheet1,Sheet1[Time])
Var current_value = maxX(Sheet1,Sheet1[Flow (m3/h)])
Var Previous_time = maxx(filter(all(Sheet1[Time]),Sheet1[Time]<current_time),Sheet1[Time])
Var previous_value = calculate(max(Sheet1[Flow (m3/h)]),all(Sheet1[Time]),Sheet1[time]=Previous_time)
var Increment = (current_value-previous_value)/2+previous_value
return  Increment 

 measure3：

Volume(m3) = sumx(Sheet1,if(isblank([Previous_time]),0,[Increment]))

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thanks amitchandak, I tried to include a very simple example of the output I need.

Kind regards,