Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculate count based on some condition

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.

 

Need Help
Show and Tell

3 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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.