The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have the following Table let us say, Table1,
i am trying to calculate the runrate in the last column, logic is that I have another Table , let us say Setup. So the Setup table has a column CP, which states the current period "5".
I want the last column for all periods upto P5 (based on the Setup[CP] value) , be equal to the value column in the table1 , and P6 to P13 onwards the be the runrate which is sum of Period 1- Period 5, divided by 5 (Which is based on Setup[CP] value.
is it possible to do it this way? Any help would be appreciated 🙂
Solved! Go to Solution.
@UrAvgWally
Apologies, that was my mistake
RunRate =
VAR RefPeriod =
MAX ( Setup[CP] )
VAR CurrentPeriod = Table1[PeriodNo]
VAR CurrentValue = Table1[Value]
VAR FilteredTable =
FILTER ( Table1, Table1[PeriodNo] <= RefPeriod )
VAR RunRate =
AVERAGEX ( FilteredTable, Table1[Value] )
RETURN
IF ( CurrentPeriod <= RefPeriod, CurrentValue, RunRate )
Hi @UrAvgWally
Please try
RunRate =
VAR RefPeriod =
MAX ( Setup[CP] )
VAR CurrentValue = Table1[Value]
VAR FilteredTable =
FILTER ( Table1, Table1[PeriodNo] <= RefPeriod )
VAR RunRate =
AVERAGEX ( FilteredTable, Table1[Value] )
RETURN
IF ( CurrentValue <= RefPeriod, CurrentValue, RunRate )
Hi @tamerj1 ,
Thanks for getting back to me!
Unfortunately not getting desired results .I was hoping Period 1 to 5, would match the value column, and 6-13 would be runrate:
@UrAvgWally
Apologies, that was my mistake
RunRate =
VAR RefPeriod =
MAX ( Setup[CP] )
VAR CurrentPeriod = Table1[PeriodNo]
VAR CurrentValue = Table1[Value]
VAR FilteredTable =
FILTER ( Table1, Table1[PeriodNo] <= RefPeriod )
VAR RunRate =
AVERAGEX ( FilteredTable, Table1[Value] )
RETURN
IF ( CurrentPeriod <= RefPeriod, CurrentValue, RunRate )
@tamerj1 , I have modified this table to include funders and since there are multiple periods as each funder will have P1- 13 values, I cannot figure a workaround. The funder is an important slicer for my dashboard, hence needed to add it.. I posted it here, if you have time , would appreciate if you can have a look. :
Thanks, worked perfectly :).
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
7 |