Calculate count based on some condition

[Anonymous]

I have a data like this.

I wants to calculate count of type based on two condition....

1)Count for type where endt=0 for types(Cancel,endt)

2)Count for type where endt greater than 0 for types (new,renewal).

Based on this calculation i want to create report like this.

[selimovd]

Hello @Anonymous 

try the following 2 measures:

Count 1 =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[endt] = 0,
    FILTER( myTable, myTable[type] = "Cancel" || myTable[type] = "endt" )
)

And for your second case:

Count 2 =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[endt] > 0,
    FILTER( myTable, myTable[type] = "new" || myTable[type] = "renewal" )
)

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[Anonymous]

hello,

 Actually i want to show both these count measures as together for using in matrix table as a column....Is it possible?

[v-kkf-msft]

Hi @Anonymous ,

Try the following formula:

Measure = 
    var Count1 = 
        CALCULATE(
            COUNTROWS( 'Table' ),
            'Table'[endt] = 0,
            FILTER( 
                'Table', 
                'Table'[type] = "Cancel" || 'Table'[type] = "endt" 
            )
        )
    var Count2 = 
        CALCULATE(
            COUNTROWS( 'Table' ),
            'Table'[endt] > 0,
            FILTER( 
                'Table', 
                'Table'[type] = "new" || 'Table'[type] = "renewal" 
            )
        )
    var result = 
        IF(
            MAX('Table'[type]) = "Cancel" || MAX('Table'[type]) = "endt" ,
            "A",
            "B"
        )
    return 
        IF(
            result = "A",
            Count1,
            Count2
        )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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