Forum Discussion
Average conditioned
Hello everyone,
I come back here because i got great answers last time i ask my questions here.
I would like to solve the problematic following :
I have two data tables in PBI. They are linked to each other with two columns key.
This is like :
| Table 1 | |
| ID | Hospital |
| Key1 | A |
| Key2 | B |
| Key3 | C |
| Key4 | D |
| TABLE 2 | |||
| ID | Phase | Take into account | progress |
| Clé 1 | P1 | 1 | 20% |
| Clé 1 | P2 | 1 | 10% |
| Clé2 | P1 | 1 | 80% |
| Clé2 | P1 | 1 | 10% |
| Clé2 | P2 | 0 | 15% |
| Clé3 | P1 | 1 | 60% |
| Clé4 | P1 | 1 | 70% |
I would like to :
For each hospital, find the average of the progress by phases, with only activites flagged by 1 (activites to take into account).
Imo, it's an average of the progresse on the Table 1, conditioned by phases and by the column "Take into account". But, i am a beginner in DAX, and i don't knwo how to do it !
Results should be :
| RESULTS | ||
| Hospital | Phases | Progress |
| A | P1 | 20% |
| A | P2 | 10% |
| B | P1 | 45% |
| B | P2 | - |
| C | P1 | 60% |
| C | P2 | - |
| D | P1 | 70% |
| D | P2 | - |
Can Someone give me clue to find these progress average ?
Thank you for your help !
Séb
2 Replies
Did something go haywire in the posting? Is the ID in Table 2 supposed to match the ID in Table 1? Key1, Key2...?
Assuming the answer is yes, perhaps:
AverageProgress = VAR __tmpTable = FILTER('Progress',[Take into account]=1) RETURN AVERAGEX(__tmpTable,[progress])Put into a table along with Hospital and Phase. Make sure Hospital and Phase are related based on [ID] columns.
