Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Insert new calculated row

Hi community, 

 

I have a calculated table like this:

for each date and project type "Fix Price", I have a calculated loss. In this case, for the project of the second line I have a calculated loss of 20h.

The donut chart by billability would be.

 

 

 

 

 

 

 

 

 

My goal is to correct this donut chart by taking into account the calculated losses, so the table should be looking like this:

 

 



 

The total hours in this case did not change, it still at 100h.

 

I attached the pbi file

thanks.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi again, 

     

    I was able to make it work 🙂

    first I tried to use UNION function with the combination of ROW function, but it did not work since ROW function returns only 1 calculated row.

     

    NewTable = UNION(Sheet1,
                     SELECTCOLUMNS(Sheet1,"Date",CALCULATE(max(Sheet1[Date]),FILTER(Sheet1,Sheet1[Losses]>0&&Sheet1[No]=EARLIER(Sheet1[No]))), 
                                  "Project Type",IF(Sheet1[Losses]>0,"loss",Sheet1[Project Type]),"Hours",Sheet1[Hours],"Billabulity",IF(Sheet1[Losses]>0,"Non Billable",Sheet1[Billability]),"Losses",CALCULATE(Max(Sheet1[losses]),FILTER(Sheet1,Sheet1[Losses]>0&&Sheet1[No]=EARLIER(Sheet1[No]))),"hours after correction",IF(Sheet1[Losses]>0&&Sheet1[Project Type]="Fix Price",Sheet1[Losses],IF(Sheet1[Billability]="Non Billable",0,Sheet1[hours after correction])),"No",Sheet1[No]))

     

    Using SELECTCOLUMNS function instead gave me the possibility to have multiple calculated rows, I attach the pbi file here.

     

    Thanks, 

    Abdel. 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi again, 

     

    I was able to make it work 🙂

    first I tried to use UNION function with the combination of ROW function, but it did not work since ROW function returns only 1 calculated row.

     

    NewTable = UNION(Sheet1,
                     SELECTCOLUMNS(Sheet1,"Date",CALCULATE(max(Sheet1[Date]),FILTER(Sheet1,Sheet1[Losses]>0&&Sheet1[No]=EARLIER(Sheet1[No]))), 
                                  "Project Type",IF(Sheet1[Losses]>0,"loss",Sheet1[Project Type]),"Hours",Sheet1[Hours],"Billabulity",IF(Sheet1[Losses]>0,"Non Billable",Sheet1[Billability]),"Losses",CALCULATE(Max(Sheet1[losses]),FILTER(Sheet1,Sheet1[Losses]>0&&Sheet1[No]=EARLIER(Sheet1[No]))),"hours after correction",IF(Sheet1[Losses]>0&&Sheet1[Project Type]="Fix Price",Sheet1[Losses],IF(Sheet1[Billability]="Non Billable",0,Sheet1[hours after correction])),"No",Sheet1[No]))

     

    Using SELECTCOLUMNS function instead gave me the possibility to have multiple calculated rows, I attach the pbi file here.

     

    Thanks, 

    Abdel.