Calculated row/cell in Power BI

[eamclaughlin]

Hi, 

I am new to Power BI and trying to resolve a problem which I have detailed below. The ideal solution would be to input a calculation at a cell level for financial years and totals although unsure if this is possible. Any help would be appreciated in solving this;

I have a table 1 as shown below;

I have then created a table via "enter data" to have a specific layout that I am looking for;

I am looking to input a calculation that will sum my financial years and input to Table 1, e.g sum(FY18) = Task 1 FY1, sum(FY19) = Task 1 FY2 etc. Row 1 of performance Task 1 is the only row to be inputted to and then the 2 total rows would calculate downwards. Below is an example of what this would look like when complete;

This is only used to export to excel to be used for another purpose within financial reporting. Table 1 and Table 2 are not linked in any way as Table 1 is my raw data, where Table 2 is one I have simply created within Power BI. 

Please someone help with an answer! Thanks in advance

Eamon

[v-danhe-msft · Accepted solution]

Hi @eamclaughlin,

Based on my test, you could refer to below steps:

Sample data:

Create four calculated columns in Table2：

FY1 = IF('Table2'[Performence]="Task 1",CALCULATE(SUM(Table1[FY18])))

FY2 = IF('Table2'[Performence]="Task 1",CALCULATE(SUM(Table1[FY19])))

FY3 = IF('Table2'[Performence]="Task 1",CALCULATE(SUM(Table1[FY20])))

FY4 = IF('Table2'[Performence]="Task 1",CALCULATE(SUM(Table1[FY21])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[eamclaughlin]

Hi @v-danhe-msft

Thank you very much for the help with this!! I was trying this method although using measures which didn't work so thanks for helping me with that error. 

One other question as an add on - Using your equation for calculated columns, Am I able to insert an OR statement in to that to calculate 2 rows of data?

e.g IF(Performance = Task1 OR Task 2, SUM(FY18))

I am would be looking to use this to calculate 3 rows out of 50 although the equation is the same within each row. I have tried the below equation but it hasn't worked;

FY1 = IF(OR('Table2'[Performence]="Task 1",'Table2'[Performence]="Task 2"),CALCULATE(SUM(Table1[FY18])))

 I receive the below error;

The syntax for ')' is incorrect. (DAX(IF(OR('Table2'[Performence]="Task 1",'Table2'[Performence]="Task 2"),CALCULATE(SUM(Table1[FY18]))).

[eamclaughlin]

Issue Resolved - I had brackets in the wrong place. 

Thanks for the help again @v-danhe-msft!!