Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to create a summary table using data similar to this
BusinessDesc | OfficeDesc | ReferenceID | ServiceRequestBankedWorkFlag | ServiceRequestBankedWorkDueInDays | ServiceRequestBankedWorkOverdueDays | ServiceRequestBankedWorkEstimatedCost | WorkOrderDaysTargetToCompletion | WorkOrderDaysRaisedToCompletion | WorkOrderDaysRaisedToInvoice |
project 1 | office 1 | 10100883 | yes | 16 | 25 | $76.00 | 83 | 97 | 97 |
project 2 | office 2 | 10124521 | No | 37 | 20 | $191.00 | 83 | 97 | 97 |
project 1 | office 3 | 10140103 | No | 32 | 5 | $153.00 | 83 | 97 | 97 |
project 2 | office 4 | 10068825 | yes | 54 | 21 | $75.00 | 97 | 97 | 97 |
project 1 | office 1 | 10140198 | No | 11 | 24 | $69.00 | 83 | 97 | 97 |
project 2 | office 2 | 10130735 | No | 45 | 15 | $167.00 | 83 | 97 | 97 |
project 1 | office 3 | 10126629 | yes | 37 | 20 | $142.00 | 83 | 97 | 97 |
project 2 | office 4 | 10115992 | No | 20 | 26 | $98.00 | 83 | 97 | 97 |
project 1 | office 1 | 10086427 | No | 31 | 15 | $63.00 | 97 | 98 | 98 |
project 2 | office 2 | 10142473 | Yes | 56 | 8 | $89.00 | 83 | 97 | 97 |
In the Summary Table Only ServiceRequestBankedWorkFlag = "Yes" are to be counted and summary table need to look like this below
Office 1 | Project 1 | ||||
Total Overdue SRs | 1 | Total Cost Overdue SRs | For combination of OfficeCode ServiceRequestFundingProgram where ServiceRequestBankedWorkOverdueDays is greater than 0 | ||
Total Due SRs (0-30 days) | 1 | Total Cost Due SRs (0-30 days) | For combination of OfficeCode ServiceRequestFundingProgram where ServiceRequestBankedWorkDueInDays is between 0 and 30 | ||
Total Banked SRs | 4 | Total Cost | For combination of OfficeCode ServiceRequestFundingProgram |
Solved! Go to Solution.
Hi @Sir_night
I created a summary table. If it isn’t the one you want, please post an example showing how to evaluate.
Table = CALCULATETABLE ( SUMMARIZE ( 'Table1', 'Table1'[BusinessDesc], 'Table1'[OfficeDesc], "Total Overdue SRs", CALCULATE ( COUNT ( Table1[ServiceRequestBankedWorkOverdueDays] ), 'Table1'[ServiceRequestBankedWorkOverdueDays] > 0 ), "Total Cost Overdue SRs", CALCULATE ( SUM ( Table1[ServiceRequestBankedWorkEstimatedCost] ), 'Table1'[ServiceRequestBankedWorkOverdueDays] > 0 ), "Total Due SRs", CALCULATE ( COUNT ( Table1[ServiceRequestBankedWorkDueInDays] ), 'Table1'[ServiceRequestBankedWorkDueInDays] >= 0 && 'Table1'[ServiceRequestBankedWorkDueInDays] <= 30 ), "Total Cost Due SRs", CALCULATE ( SUM ( Table1[ServiceRequestBankedWorkEstimatedCost] ), 'Table1'[ServiceRequestBankedWorkDueInDays] >= 0 && 'Table1'[ServiceRequestBankedWorkDueInDays] <= 30 ), "Total Banked SRs", COUNT ( Table1[ServiceRequestBankedWorkFlag] ), "Total Cost", SUM ( Table1[ServiceRequestBankedWorkEstimatedCost] ) ), Table1[ServiceRequestBankedWorkFlag] = "yes" )
Best Regards!
Dale
Hi @Sir_night
I created a summary table. If it isn’t the one you want, please post an example showing how to evaluate.
Table = CALCULATETABLE ( SUMMARIZE ( 'Table1', 'Table1'[BusinessDesc], 'Table1'[OfficeDesc], "Total Overdue SRs", CALCULATE ( COUNT ( Table1[ServiceRequestBankedWorkOverdueDays] ), 'Table1'[ServiceRequestBankedWorkOverdueDays] > 0 ), "Total Cost Overdue SRs", CALCULATE ( SUM ( Table1[ServiceRequestBankedWorkEstimatedCost] ), 'Table1'[ServiceRequestBankedWorkOverdueDays] > 0 ), "Total Due SRs", CALCULATE ( COUNT ( Table1[ServiceRequestBankedWorkDueInDays] ), 'Table1'[ServiceRequestBankedWorkDueInDays] >= 0 && 'Table1'[ServiceRequestBankedWorkDueInDays] <= 30 ), "Total Cost Due SRs", CALCULATE ( SUM ( Table1[ServiceRequestBankedWorkEstimatedCost] ), 'Table1'[ServiceRequestBankedWorkDueInDays] >= 0 && 'Table1'[ServiceRequestBankedWorkDueInDays] <= 30 ), "Total Banked SRs", COUNT ( Table1[ServiceRequestBankedWorkFlag] ), "Total Cost", SUM ( Table1[ServiceRequestBankedWorkEstimatedCost] ) ), Table1[ServiceRequestBankedWorkFlag] = "yes" )
Best Regards!
Dale
hi dale
thanks just what i needed
regards
David
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |