Summarize

[karkar]

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%

[Greg_Deckler]

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]

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 · Accepted solution]

---

@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]

[karkar]

Hello Eric,

Thank you for providing the DAX. I notice that with Measures we get correct output.

I am very curious to learn as to why we are getting the following results with creating columns like shown below:

col1 = countrows(Sheet1) gives

ID      NAME   COL1

101  Jenny        24

102  Penny       6

103  Manny      6

col2 = CALCULATE( COUNTROWS('Sheet1'), ALLEXCEPT(Sheet1,Sheet1[ID]))  gives:

ID      NAME   COL2

101  Jenny        16

102  Penny       1

103  Manny      1

col3 = CALCULATE( COUNTROWS('Sheet1'),
ALLEXCEPT(Sheet1,Sheet1[ID],Sheet1[NAME]))

ID      NAME   COL3

101  Jenny        16

102  Penny       1

103  Manny      1

[Eric_Zhang]

---

@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]

[bsas]

@karkar

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

[karkar]

Hello ,

Denominator is Count of unique ID/NAME

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