Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I tried to make a control chart in powerbi dashboard where the central line needs to be the average of the first top 30, sort on date created. The same goes for the STDEV. The channel_1_cq is going to be the line chart in these control chart. The goal is to be able to select instrument ID and lot filter slicer and see the values of the channel_1_cq changes
However, The average and STDEV needs to fixed and not be influeced by instrument ID. But it does needs to be influenced by lot
Table name: kit
Sample ID | Lot | Instrument ID | Channel_1_cq | Date created |
P1 | A | C0044 | 29.892 | 24/03/2021 17:56 |
P2 | B | C0044 | 29.89 | 24/03/2021 19:40 |
N3 | C | C-0040 | 29.88 | 24/03/2021 21:32 |
calculated columns for control chart:
STDEVXTOP30 = CALCULATE( STDEVX.S(TOPN(30,'Kit','Kit'[Date created],ASC),'kit'[Channel_1_Cq]),ALLSELECTED('kit'))
STDEVX.S
If i select machine IDs both the central line and SD changes depending on the machine i select. How can i ignore the machine ID part only for the control chart?
Hi @amitchandak,
I already tried it, but it still shifts. It seems not to work in combination with all select. I also tried ALLEXCEPT('kit'[LOT]) which seems to be able to fixed. But my average number suddently shifts from 30.02 to 29.92. and it is fixed on 29.92 which is not the right number.
I have figure out why the number change. I forgot to mention that the top30 is only based sample ID starting with 'P'. I have a hard filter saying only shows sample ID starting with "P'. However all except still ignores this and calculate top 30 ignoring if it is an P sample or a N sample.
My calc now instead of all select is ALLEXCEPT with kit [lot] and kit [sample id]
ps: i can not post the calc, i get a html error
@Anonymous , see if removefilters can help
example
CALCULATE( STDEVX.S(TOPN(30,'Kit','Kit'[Date created],ASC),'kit'[Channel_1_Cq]),ALLSELECTED('kit'), removefilters('Kit'[MachineID]))
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |