creating calculated row in a table which shares same table column headers

[agbaya]

Hello community - i need your expertise.

I have a table which 'll love to create new calculated rows to get variance between 2 reporting period e.g. variance between reporting period 1 and period 2

Reporting period	Team	Budget	Actual ITD	Final forecast
Period 1	team1	10	5	12
Period 1	team2	15	7	12
Period 1	team3	20	9	20
Period 2	team1	10	7	12
Period 2	team2	15	9	13
Period 2	team3	20	13	17

What i intend to create in pbi is a table that looks like below..... intention is to create calculated rows which calculates the variance between the 2 reporting period using existing filters

Reporting period	Team	Budget	Actual ITD	Final forecast
Period 1	team1	10	5	12
Period 1	team2	15	7	12
Period 1	team3	20	9	20
Period 2	team1	10	7	12
Period 2	team2	15	9	13
Period 2	team3	20	13	17
VAR	team1	0	2	0
VAR	team2	0	2	1
VAR	team3	0	4	-3

Although i've limited the rows to just 3 for these example in reality - i have about 900 rows for each period so hoping i dont have to create a row for each line?

Any suggestion would be appreciated.

Thank you

[v-yalanwu-msft]

hi, @agbaya ;

Could you please tell me if your problem has been solved?

If it is, could you please mark the helpful replies as a solution to close this topic and help others can learn from it ?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi @agbaya  , 

As @Fowmy said ,You could try to create three measure to calculate difference, as follows:

DiffBudget =
VAR _value =
    MAX ( [Budget] )
        - CALCULATE (
            MAX ( [Budget] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Team] ),
                [Reporting period] < MAX ( [Reporting period] )
            )
        )
RETURN
    IF (
        MAX ( [Reporting period] ) <> MINX ( ALL ( 'Table' ), [Reporting period] ),
        _value
    )

DiffActual IT =
VAR _value =
    MAX ( [Actual ITD] )
        - CALCULATE (
            MAX ( [Actual ITD] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Team] ),
                [Reporting period] < MAX ( [Reporting period] )
            )
        )
RETURN
    IF (
        MAX ( [Reporting period] ) <> MINX ( ALL ( 'Table' ), [Reporting period] ),
        _value
    )

DiffFinal =
VAR _value =
    MAX ( [Final forecast] )
        - CALCULATE (
            MAX ( [Final forecast] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Team] ),
                [Reporting period] < MAX ( [Reporting period] )
            )
        )
RETURN
    IF (
        MAX ( [Reporting period] ) <> MINX ( ALL ( 'Table' ), [Reporting period] ),
        _value
    )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[Fowmy]

@agbaya 

I think you should consider creating measures to show the variances between the selected 2 periods in your report. Creating variance rows and appending them to your table would not give you the options to visualize effectively.  You also mentioned 900 records, therefore try a measure approach.
 

[agbaya]

Thanks @Fowmy - would these measures be created as columns? could you expantiate how this would work.

Thanks for your help