Forum Discussion

bcardenas's avatar
bcardenas
Frequent Visitor
6 years ago
Solved

Distinct Count filter with sum

Hello, I am a newbie trying to count Lenders w/ 4 or more certs for a year.  I am trying this formula but it does not filter >3, it just gives me a straight count.   Active Lenders 1 yr = CALCULA...
  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    bcardenas Sorry, I didn't see that you have posted the data, you can try this:

    Total Certificate = SUM ( Certificates[certified] )

     

    Active Lenders 1 yr = 
COUNTROWS (
    FILTER (
        SUMMARIZE ( Certificates, Certificates[InstitutionID], 'Date'[Year] ),
        [Total Certificate] > 3
    )
)

     

    Model

AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
6 years ago

bcardenas Try this:

 

Active Lenders 1 yr =
CALCULATE (
    DISTINCTCOUNT ( Certificates[institutionID] ),
    FILTER (
        ALL ( Certificates ),
        CALCULATE ( SUM ( Certificates[Certified] ) ) > 3
    )
)

 

  • bcardenas's avatar
    bcardenas
    Frequent Visitor
    6 years ago

    AntrikshSharma  when I try this solution I do not get an error but the measure is blank when pulled into a report.

    • AntrikshSharma's avatar
      AntrikshSharma
      Icon for Community Champion rankCommunity Champion
      6 years ago

      bcardenas Sorry, I didn't see that you have posted the data, you can try this:

      Total Certificate = SUM ( Certificates[certified] )

       

      Active Lenders 1 yr = 
COUNTROWS (
    FILTER (
        SUMMARIZE ( Certificates, Certificates[InstitutionID], 'Date'[Year] ),
        [Total Certificate] > 3
    )
)

       

      Model

      • bcardenas's avatar
        bcardenas
        Frequent Visitor
        6 years ago

        AntrikshSharma  Much thanks, this is exactly what I needed.