Help with distinct count and filter

[Sir_night]

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

[v-jiascu-msft · Accepted 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

[Sir_night]

hi dale

thanks just what i needed

regards

David