cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sir_night Regular Visitor
Regular Visitor

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

Accepted Solutions
Microsoft v-jiascu-msft
Microsoft

Re: Help with distinct count and filter

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
Microsoft v-jiascu-msft
Microsoft

Re: Help with distinct count and filter

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

Sir_night Regular Visitor
Regular Visitor

Re: Help with distinct count and filter

hi dale

 

thanks just what i needed

 

regards

 

David

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors