Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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%
Solved! Go to 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
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]
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
@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]
from your example second table is not clear. What are "Denom" and "Neum" columns and what data do you resresent there?
Hello ,
Denominator is Count of unique ID/NAME
Numerator is the count of rows within each ID/NAME combinations which had a value.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |