Forum Discussion
Help with these two calculations please!
I have been asked to create two calculations in Power BI (either using Power Query or DAX). With the way the data is structure I'm struggling to understand the best way to do these calculations.
The data structure is:
Value Period Type FiscalYear
0 P3 PLANNED 2016
33 P3 REVISED 2016
0 P3 ACTUAL 2016
100 P4 PLANNED 2016
0 P4 REVISED 2016
0 P4 ACTUAL 2016
6000 P1 PLANNED 2017
0 P1 REVISED 2017
0 P1 ACTUAL 2017
The two calculations to be created are:
1) For each FiscalYear, and for each LEPeriod: create a field named "CurrentPlanned"=IF(REVISED>0, REVISED, PLANNED)
2) For each FiscalYear, and for each LEPeriod: create a field named "Variation"=CurrentPlanned - ACTUAL
How would you achieve this?
The dataset above repeats for many more years so I wouldn't hardcode the FiscalYear.
Your prompt response will be much appreacited.
Thanks.
In Power Query: just pivot on column Type with value column Value, advanced option "Don't aggregate" (or leave the default "Sum" in cae of duplicate records), then add your 2 columns.
Remark: I added both columns as a custom column; the first column can also be added as a conditional column.
let Source = Table1, #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Type]), "Type", "Value"), #"Added Custom" = Table.AddColumn(#"Pivoted Column", "CurrentPlanned", each if [REVISED] > 0 then [REVISED] else [PLANNED]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Variation", each [CurrentPlanned] - [ACTUAL]) in #"Added Custom1"
8 Replies
- vanessafvg
Community Champion
Anonymous
for starters have you created a date table?
- vanessafvg
Anonymous i guess what i am trying process is where the blocker is, would it not make sense to create 2 columns, i.e planned and revised become 2 columns. this is easy to do in power query, just pivot on those 2 columns and set value to be the value
