Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sir_night
Helper I
Helper I

Help with distinct count and filter

I'm trying to create a summary table using data similar to this

 

BusinessDescOfficeDescReferenceIDServiceRequestBankedWorkFlagServiceRequestBankedWorkDueInDaysServiceRequestBankedWorkOverdueDaysServiceRequestBankedWorkEstimatedCostWorkOrderDaysTargetToCompletionWorkOrderDaysRaisedToCompletionWorkOrderDaysRaisedToInvoice
project 1office 110100883yes1625 $76.00 839797
project 2office 210124521No3720 $191.00 839797
project 1office 310140103No325 $153.00 839797
project 2office 410068825yes5421 $75.00 979797
project 1office 110140198No1124 $69.00 839797
project 2office 210130735No4515 $167.00 839797
project 1office 310126629yes3720 $142.00 839797
project 2office 410115992No2026 $98.00 839797
project 1office 110086427No3115 $63.00 979898
project 2office 210142473Yes568 $89.00 839797

 

In the Summary Table Only ServiceRequestBankedWorkFlag = "Yes" are to be counted and summary table need to look like this below

 

Office 1Project 1 
Total Overdue SRs1Total Cost Overdue SRs  For combination of OfficeCode ServiceRequestFundingProgram where ServiceRequestBankedWorkOverdueDays is greater than 0
Total Due SRs (0-30 days)1Total Cost Due SRs (0-30 days)  For combination of OfficeCode ServiceRequestFundingProgram where ServiceRequestBankedWorkDueInDays is between 0 and 30
Total Banked SRs4Total Cost  For combination of OfficeCode ServiceRequestFundingProgram
1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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"
)

Help with distinct count and filter.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!
Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jiascu-msft
Employee
Employee

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"
)

Help with distinct count and filter.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!
Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi dale

 

thanks just what i needed

 

regards

 

David

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.