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 u...
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

     

     

     

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

eamclaughlin's avatar
eamclaughlin
Frequent Visitor
7 years ago

Issue Resolved - I had brackets in the wrong place. 

 

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