Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Right, so if this is a column, it is pretty standard:
Column = SUMX(FILTER('Table',[Seq] <= EARLIER('Table'[Seq])),[Energy Consumption])
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
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'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])
This is brilliant....fixed my problem. thanks
This issue had been stuck me for past three days!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |