Forum Discussion

eamclaughlin's avatar
eamclaughlin
Frequent Visitor
7 years ago
Solved

Calculated row/cell in Power BI

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

General Comment
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

     

     

     

3 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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's avatar
      eamclaughlin
      Frequent Visitor
      7 years ago

      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]))).