Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to count it units>0

Hi guys,

Data is stored in below formats but I have to count at summary 2 level if units>0.

 

Raw data   
CodeUnits  
AP500028621051  
AP50002862105-1  
AP520011608993  
BI800001609436  
    
I have to count at Code level if units>0 
    
CodeUnitsfinal output 
AP5000286210500 
AP5200116089931 
BI8000016094361 
  2 

Please suggest. Thanks a lots.

Tanushree_Kapse amitchandak Greg 

COUNTAX
COUNTX
  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    5 years ago

    Hi Anonymous ,

     

    Use these measures:
    First calculate the count:

     
    Count = Var a= CALCULATE(SUM('Table (3)'[Units]))
    return IF(a>0, COUNT('Table (3)'[Code]),0)

    second To get the correct total:
    Fix = SUMX(VALUES('Table (3)'[Code]),[Count])
     
    Mark this as a solution, if I answered your question. Kudos are always appreciated.
    Thanks.

6 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    5 years ago

    Hi Anonymous ,

     

    Please find the Screenshot:

     

    Count = VAR a= CALCULATE(COUNT('Table (3)'[Units]),'Table (3)'[Units]> 1)
    return IF(a=BLANK(),0,a)
     
     
    Mark this as a solution, if I answered your question. Kudos are always appreciated.
    Thanks.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for reply.

      I have to count >0 units not >1 units.

      I have applied the logic already as you mentioned but when I check at code level it is counting where units is 0 as well . like in table 2. first code having units 0 idealy count should not happen at that row.

      please check

      • Tanushree_Kapse's avatar
        Tanushree_Kapse
        Impactful Individual
        5 years ago

        Hi Anonymous ,

         

        Use these measures:
        First calculate the count:

         
        Count = Var a= CALCULATE(SUM('Table (3)'[Units]))
        return IF(a>0, COUNT('Table (3)'[Code]),0)

        second To get the correct total:
        Fix = SUMX(VALUES('Table (3)'[Code]),[Count])
         
        Mark this as a solution, if I answered your question. Kudos are always appreciated.
        Thanks.