cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
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

 

SeqTimeDPAirflowEnergyEnergyConsumption 
1031.784.75780.00000.0000 
20.333328.734.39690.14030.1403=0+0.1403
30.166728.854.41130.07070.2111=0.1403+0.2111
40.166728.434.36100.06890.2800=0.2111+0.28
50.166729.054.43520.07160.3515=0.28+0.3515
60.166725.373.98950.05620.4078=0.3515+0.4078
70.166731.054.67210.08060.4884=0.4078+0.4884
80.166726.94.17640.06240.5508=0.4884+0.5508
90.166732.874.88490.08920.6400=0.5508+0.64
100.166726.744.15700.06180.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

@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])

 

 


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Super User IV
Super User IV

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

 

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

 

 


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




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

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

 

 

 

 


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




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

@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])

 

 


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




View solution in original post

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors