Forum Discussion
Resolver IIAverage weeks with a certain status
Hi There,
I have data with ID's, Date, Week Rank, last week status & this week status. I need a measure for the average number of weeks an ID stays at a certain status.
| ID | Week Rank | Last week status | This week status |
| A | 38 | G | R |
| B | 38 | A | A |
| C | 38 | G | G |
| A | 39 | R | R |
| B | 39 | A | R |
| C | 39 | G | R |
| A | 40 | R | R |
| B | 40 | R | A |
| C | 40 | R | R |
Results
Count of weeks at R
A = 3
B = 1
C = 2
Average weeks at R = (3+1+2)/3 = 2 weeks
Thank you!!!!!!!!!
Hayleysea , Try like
AverageX(values(Table[ID]),DistinctCount(Table[Week Rank]))
AverageX(summarize(Table[ID],"_1",DistinctCount(Table[Week Rank])),[_1])
Hi, Hayleysea
It’s my pleasure to answer for you.
According to your description, I think you can create a measure to calculate the 'average weeks'.
If you want to calculate the number without considering the filters, try like this:
average weeks = DIVIDE ( COUNTROWS ( FILTER ( ALL ( TableR ), TableR[This week status] = "R" ) ), CALCULATE ( DISTINCTCOUNT ( TableR[ID] ), ALL ( TableR ) ) )If you want to calculate the number considering the filters, you can change all to allselected, try like this:
2average weeks = DIVIDE ( COUNTROWS ( FILTER ( ALLSELECTED ( TableR ), TableR[This week status] = "R" ) ), CALCULATE ( DISTINCTCOUNT ( TableR[ID] ), ALLSELECTED ( TableR ) ) )If you have other questions, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Hayleysea , Try like
AverageX(values(Table[ID]),DistinctCount(Table[Week Rank]))
AverageX(summarize(Table[ID],"_1",DistinctCount(Table[Week Rank])),[_1])
- v-janeyg-msft
Community Support
Hi, Hayleysea
It’s my pleasure to answer for you.
According to your description, I think you can create a measure to calculate the 'average weeks'.
If you want to calculate the number without considering the filters, try like this:
average weeks = DIVIDE ( COUNTROWS ( FILTER ( ALL ( TableR ), TableR[This week status] = "R" ) ), CALCULATE ( DISTINCTCOUNT ( TableR[ID] ), ALL ( TableR ) ) )If you want to calculate the number considering the filters, you can change all to allselected, try like this:
2average weeks = DIVIDE ( COUNTROWS ( FILTER ( ALLSELECTED ( TableR ), TableR[This week status] = "R" ) ), CALCULATE ( DISTINCTCOUNT ( TableR[ID] ), ALLSELECTED ( TableR ) ) )If you have other questions, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.