Forum Discussion
Solved
Averaging Distinct Counts in a Pivot Table
OK, I've created a Pivot Table that pulls the distinct count of patient identifiers per date in one column by the date in the rows from a query. I'm trying to get a calculation of the average daily ...
You may add measure as
Daily Average of Unique Patients:=AVERAGEX( SUMMARIZE( Range, Range[DATE], "Daily Unique", DISTINCTCOUNT(Range[Patient ID]) ), [Daily Unique])Result is like
Hi, I'm trying to do something similar. I've Purchase Order numbers, split per line. I'm looking to get the average order value per PO. It's easy to get the average value per line, but what I need is the average value per distinct PO number. I've attached an excel as an example, but my dataset is much larger. In the example the average value is 288 for everything but I need to know the average of each PO. The value I need is £396.10 which is the total for each PO number divided by the amount of PO's placed (8 in this example).
| PO Number | PO Line Number | PO Value GBP Equiv |
| O033314 | 001 | 193.5 |
| O033314 | 002 | 50.08 |
| O033315 | 001 | 13.23 |
| O033315 | 002 | 34.79 |
| O033316 | 001 | 11.11 |
| O033317 | 001 | 95 |
| O033318 | 001 | 1465 |
| O033319 | 001 | 248.2 |
| O033320 | 001 | 100 |
| O033321 | 001 | 954.78 |
| O033321 | 002 | 3.13 |