Forum Discussion
Summarize
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
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]
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.
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 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
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 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
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]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