Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |