Forum Discussion

karkar's avatar
karkar
Helper III
8 years ago
Solved

Summarize

Hello, I have somethng like shown below.

 Days_to_come_back will have a value less than or equals 30 if at all present. Otherwise they will all be blanks.

Could you help me to get the output shown??

 

HAVE

ID      NAME          Days_to_come_back

1        Jenny        

1        Jenny                    13                

1        Jenny                  

1        Jenny                     12              

2       Penny

3      Manny

          

WANT:

ID      NAME          Denom    Neum    Rate

1        Jenny             4              2          (2/4)*100=50.00%

2        Penny            1              0          (0/1)*100=0.00%

3      Manny             1              0          (0/1)*100=0.00%

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago
    karkar wrote:

    Helo smoupre,

     

    I tried to do the denominator and all the rows gettign a value of 6

     

    For Jenny denom should be 4 since there is 4 records

    For Penny denom should be 1 since there is a single record

    For Manny denom should be 1 since there is a single record

     

    For Jenny neum should be 2 since there is 2 records for Jenny which had a value for Days_to_come_back

    For Penny and Manny, Neum should be 0 since they both dont have a value under Days_to_come_back

     

    Generally the value under Days_to_come_back if exists has a value from 1-30

     

    karkar

     

    You can try to create 3 measures as below

    denom =
CALCULATE (
    COUNTROWS ( 'Summarize' ),
    ALLEXCEPT ( 'Summarize', 'Summarize'[ID], 'Summarize'[Name] )
)

neum =
IF (
    ISBLANK (
        COUNTROWS ( FILTER ( 'Summarize', 'Summarize'[Days_to _come_back] > 0 ) )
    ),
    0,
    COUNTROWS ( FILTER ( 'Summarize', 'Summarize'[Days_to _come_back] > 0 ) )
)
rate = [neum1]/[denom]

7 Replies

  • bsas's avatar
    bsas
    Post Patron
    8 years ago

    karkar

     

    from your example second table is not clear. What are "Denom" and "Neum" columns and what data do you resresent there?

    • karkar's avatar
      karkar
      Helper III
      8 years ago

      Hello ,

       

      Denominator is Count of unique ID/NAME

      Numerator is the count of rows within each ID/NAME combinations which had a value.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago
    Denom = COUNTROWS('Summarize')

Neum = VAR mycount = COUNT('Summarize'[Days_to_come_back])
RETURN (IF(ISBLANK(mycount),0,mycount))

Rate = DIVIDE([Neum],[Denom])

    Create a table visualization with ID, NAME, Denom, Neum and Rate.

    • karkar's avatar
      karkar
      Helper III
      8 years ago

      Helo smoupre,

       

      I tried to do the denominator and all the rows gettign a value of 6

       

      For Jenny denom should be 4 since there is 4 records

      For Penny denom should be 1 since there is a single record

      For Manny denom should be 1 since there is a single record

       

      For Jenny neum should be 2 since there is 2 records for Jenny which had a value for Days_to_come_back

      For Penny and Manny, Neum should be 0 since they both dont have a value under Days_to_come_back

       

      Generally the value under Days_to_come_back if exists has a value from 1-30

       

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        8 years ago
        karkar wrote:

        Helo smoupre,

         

        I tried to do the denominator and all the rows gettign a value of 6

         

        For Jenny denom should be 4 since there is 4 records

        For Penny denom should be 1 since there is a single record

        For Manny denom should be 1 since there is a single record

         

        For Jenny neum should be 2 since there is 2 records for Jenny which had a value for Days_to_come_back

        For Penny and Manny, Neum should be 0 since they both dont have a value under Days_to_come_back

         

        Generally the value under Days_to_come_back if exists has a value from 1-30

         

        karkar

         

        You can try to create 3 measures as below

        denom =
CALCULATE (
    COUNTROWS ( 'Summarize' ),
    ALLEXCEPT ( 'Summarize', 'Summarize'[ID], 'Summarize'[Name] )
)

neum =
IF (
    ISBLANK (
        COUNTROWS ( FILTER ( 'Summarize', 'Summarize'[Days_to _come_back] > 0 ) )
    ),
    0,
    COUNTROWS ( FILTER ( 'Summarize', 'Summarize'[Days_to _come_back] > 0 ) )
)
rate = [neum1]/[denom]