cancel
Showing results for
Did you mean: Helper I

## Get Measure value based on condition DAX

Hi,

I'm facing problem to calculate current measure value + Previous row measure value

Seq, Time, DP are source data.

Airflow is calculated column = IF(DP< 0.1,0,EXP((LN(DP /4.322)/1.2791)))

Fan Efficiency (%, 1 to 100) is "What-If" parameter

Energy is measure = (DP * Airflow * Time)/(10* Fan Efficinecy)

My problem is how I can calculate the EnergyConsumption value  = Energy + Previous Energy Consumption

 Seq Time DP Airflow Energy EnergyConsumption 1 0 31.78 4.7578 0.0000 0.0000 2 0.3333 28.73 4.3969 0.1403 0.1403 =0+0.1403 3 0.1667 28.85 4.4113 0.0707 0.2111 =0.1403+0.2111 4 0.1667 28.43 4.3610 0.0689 0.2800 =0.2111+0.28 5 0.1667 29.05 4.4352 0.0716 0.3515 =0.28+0.3515 6 0.1667 25.37 3.9895 0.0562 0.4078 =0.3515+0.4078 7 0.1667 31.05 4.6721 0.0806 0.4884 =0.4078+0.4884 8 0.1667 26.9 4.1764 0.0624 0.5508 =0.4884+0.5508 9 0.1667 32.87 4.8849 0.0892 0.6400 =0.5508+0.64 10 0.1667 26.74 4.1570 0.0618 0.7018 =0.64+0.7018

Here is the Pbix file and excel file link

https://www.dropbox.com/s/5ms8azheh6pfsmt/Sample.pbix?dl=0

https://www.dropbox.com/s/iux4hnvxcyivgei/Sample.xlsx?dl=0

1 ACCEPTED SOLUTION Super User IV

@phoisan  I really felt more like going to bed rather than downloading another PBIX tonight, but here you go, updated PBIX is attached below the sig.

``````Energy Cumulative =
VAR __Seq = MAX('Test'[Seq])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Test'),[Seq]<=__Seq),
[Seq],
"Energy",[Energy]
)
RETURN
SUMX(__Table,[Energy])``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

6 REPLIES 6 Super User IV

Right, so if this is a column, it is pretty standard:

Column = SUMX(FILTER('Table',[Seq] <= EARLIER('Table'[Seq])),[Energy Consumption])

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User! Helper I

But the Energy is measure not column. How I can calculate the sum of current energy and previous row energy?

Here is the Pbix file and excel file link

https://www.dropbox.com/s/5ms8azheh6pfsmt/Sample.pbix?dl=0

https://www.dropbox.com/s/iux4hnvxcyivgei/Sample.xlsx?dl=0 Super User IV

Well, in that case, you create a table variable using SUMMARIZE or GROUPBY that is essentially the same as your visualization. You use ADDCOLUMNS to add your measure as a column. You then use ADDCOLUMNS again to add your cumulative measure as a column. You then can filter down to the correct row in the table.

The technique is essentially laid out here:  https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User! Helper I

I'm not really understand how to do it. I'm new to the Power BI.

I'll try to understand the articles you shared .

Thanks Super User IV

@phoisan  I really felt more like going to bed rather than downloading another PBIX tonight, but here you go, updated PBIX is attached below the sig.

``````Energy Cumulative =
VAR __Seq = MAX('Test'[Seq])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Test'),[Seq]<=__Seq),
[Seq],
"Energy",[Energy]
)
RETURN
SUMX(__Table,[Energy])``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User! Helper I

This is brilliant....fixed my problem. thanks 
This issue had been stuck me for past three days!   