Help with these two calculations please!

[Anonymous]

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.

[vanessafvg]

@Anonymous

for starters have you created a date table?

[Anonymous]

Yes, and it contain the normal dates, month number, etc.. but also the FiscalYear and Periods.

[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

[Anonymous]

Thanks for your suggestion but I'm afraid I don't follow. 

First of all I'm not sure if I should be attempting this using a calculated column in Power Query, or DAX.

The calculation I'm thinking goes a little something like this:

Calculated column version:
CurrentPlanned = IF( Type='REVISED' THEN
                                                                      IF( REVISED>0 THEN REVISED
                                                                                              ELSE ( SELECT PLANNED where FiscalYear = revised row's FiscalYear

                                                                                                                                      and Period = revised row's Period )                                                                                ELSE "n/a" (and we can filter out the n/a values in the report pane.

The above doen't look great but I'm not sure how to do in Power BI. Hopefully the above will make sense of what I'm trying to achieve.

DAX verison:

Don't know how to. The DAX will need to be able to work out the calculation on-the-fly using group by clauses.

Again, it will be much appreciated if someone can put me on the right track.

Thanks.

[vanessafvg]

@Anonymous look the issue here is you have values on 3 different rows  per period that you want to combine into one or 2 measures.

so the easiest way to handle it is in my opinion is by pivoting those values it so that all of the measures planned, actual and revised are on the same row  for each period so that you can easily create your measure when they all on the same row.

look at the pivot section here

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/

my suggestion is for you to pivot the values in power query to get them on one row, pivot by your period

then create your calculations (with if statements) in dax.

[Anonymous]

I'm think my head is too into it that I've lost clarity. Maybe tomorrow I'll be fresh with clear vision.

I have used the pivot function many times but I don't see how to use it in your suggestion?

What 'date' field?  There are 3 fields that make up a unique date, those being Period, Type, and FiscalYear. Am I to pivot all of them? When playing with that the data structure looks a complete mess.

In addition to Value, Period, Type, and FiscalYear fields, there are many more fields but not required for the calculations but will remain for filtering.

Are you, or someone able to knock up a dummy .pbix for the solution?  This way I can review and understand.

Thanks.

[MarcelBeug · Accepted solution]

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"

[Anonymous]

Brillant. It worked.
To fulfil the calculations, merging rows with removing null on the fly, I had to do more pivoting/unpivoting but I got there and all looks good.

Thanks.