Hello,
I'm pretty new to Power BI so please forgive me if this is trivial or unnesscarly complicated.
I have data stored like this...
| Event Date | ModalityUpdatedText | Suspected referral | Exams |
| 01/11/2018 | CT | 03 - Suspected lung cancer | 2 |
| 01/11/2018 | CT | 06 - Suspected upper gastrointestinal cancers | 2 |
| 01/11/2018 | CT | 07 - Suspected lower gastrointestinal cancers | 14 |
| 01/11/2018 | CT | 11 - Suspected urological cancers (excluding testicular) | 2 |
| 01/11/2018 | Mammography | 01 - Suspected breast cancer | 21 |
| 01/11/2018 | Mammography | 16 - Exhibited (non-cancer) breast symptoms - cancer not initially suspected | 5 |
| 01/11/2018 | MRI | 04 - Suspected haematological malignancies excluding acute leukaemia | 2 |
| 01/11/2018 | MRI | 11 - Suspected urological cancers (excluding testicular) | 1 |
| 01/11/2018 | MRI | 13 - Suspected head & neck cancers | 3 |
| 01/11/2018 | MRI | | 1 |
| 02/11/2018 | CT | 01 - Suspected breast cancer | 3 |
| 02/11/2018 | CT | 06 - Suspected upper gastrointestinal cancers | 2 |
I'm trying to create report that would allow the user to generate SPC (control) charts. This chart would change automatically based on the slicers selected. These slicers would be date, referral type or modality.
A SPC Chart has the total as a line and 3 constant lines:
- Mean
- Upper limit (mean + 3 SD)
- Lower limit (mean - 3 SD)
Basically it's just a line chart with 3 straight lines on it. However I would like these lines to change to show only data that has been selected via slicers.
I figured out a mean could be created by making a measure with the formula below and dragging it to the chart.
CALCULATE ( SUM ( exams[Exams] ), ALLSELECTED( exams ) )
/ CALCULATE ( DISTINCTCOUNT ( exams[Event Date] ), allselected( exams ) )
I tried to do the same with a standard dev measure (and then using this is product the limits) as below but this does not work.
CALCULATE( STDEV.s(exams[Exams]), allselected(exams[Event Date]) )
I think it's taking every line seperately and calculating the std dev from that but I want the std dev of the sum of exams per day, not per line.
Hopefully that's not too confusing. Thanks in advance for any advice.
