Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Consider the following sample table showing durations of each state of a call in seconds for each state (attempt, cold transfer, consults etc);
CallID | Attempt(s) | ColdTrf(s) | WarmTrf(s) | Consult(s) |
1 | 12 | 55 | 22 | 110 |
2 | 199 | 121 | 6 | 92 |
3 | 96 | 79 | 80 | 69 |
4 | 16 | 117 | 128 | 45 |
5 | 49 | 68 | 43 | 22 |
6 | 145 | 7 | 61 | 2 |
7 | 39 | 28 | 90 | 185 |
8 | 55 | 11 | 117 | 49 |
9 | 77 | 18 | 98 | 171 |
10 | 154 | 171 | 147 | 97 |
11 | 4 | 181 | 39 | 146 |
12 | 67 | 8 | 162 | 112 |
I have measures that work out the first and second standard deviations for each column;
SD | Attempt(s) | ColdTrf(s) | WarmTrf(s) | Consult(s) |
1 | 59 | 60 | 48 | 55 |
2 | 117 | 125 | 92 | 114 |
What I need is an SD filter where I can select an SD level (so 1 or 2) and it should show me the values above that SD level.
For example, if I select 1 SD in filter
CallID | Attempt(s) | ColdTrf(s) | WarmTrf(s) | Consult(s) |
1 | 110 - 55 | |||
2 | 199 - 59 | 121 - 60 | 92 -55 | |
3 | 96 -59 | 79 -60 | 80 - 48 | 69 - 55 |
4 | 117 -60 | 128 -48 | ||
5 | 68 -60 | |||
6 | 145 -59 | 61 -48 | ||
7 | 90 -48 | 185 -55 | ||
8 | 117 -48 | |||
9 | 77 - 59 | 98 -48 | 171 -55 | |
10 | 154 - 59 | 171 - 60 | 147 -48 | 97-55 |
11 | 181 -60 | 146 -55 | ||
12 | 67 - 59 | 162 -48 | 112 - 55 |
As you can see from the above table, the SD filter now has applied to all values above the SD1 value and should ignore all the ones below it (the ones that have strikethrough). So for SD 1 for Attempt it will ignore 12, 16, 49, etc as they fall below the SD1 value of 59.
What I need;
1) Filter for the SD (from calculated measures)
2) SUM values in each column that fall above that filtered SD level
So finally, when the SD filter is selected as 1, the final table summed up should look like this;
Attempt(s) | ColdTrf(s) | WarmTrf(s) | Consult(s) |
384 | 377 | 457 | 542 |
In summary im only interested in seconds above the selected SD filter.
Any help/guidence will be apprecieated.
Solved! Go to Solution.
Hi @rax99
Try this:
1. Create a table like the first one you show with the SDs (if you don't already have it). Let's call it SDTable
2. Place SDTable[SD] in a slicer. You'll use this to make the selection.
3. Place Table1[CallID] in the rows of a matrix visual
4. Create theses four measures and place them in values of the matrix:
MeasureAttempt = SUMX ( Table1, VAR _AboveSD = Table1[Attempt(s)] - SELECTEDVALUE ( SDTable[Attempt(s)] ) RETURN IF ( _AboveSD >= 0, _AboveSD ) )
MeasureColdTrf = SUMX ( Table1, VAR _AboveSD = Table1[ColdTrf(s)] - SELECTEDVALUE ( SDTable[ColdTrf(s)] ) RETURN IF ( _AboveSD >= 0, _AboveSD ) )
and create MeasureWarmTrf(s) and MeasureConsult(s) following the same logic.
This should show the value above the SD if it's >=0 and blank otherwise plus the sum of each column at the total.
I guess it could also be done in a different way without using the table and invoking your SD measures directly from the measures above.
Hi @rax99
Try this:
1. Create a table like the first one you show with the SDs (if you don't already have it). Let's call it SDTable
2. Place SDTable[SD] in a slicer. You'll use this to make the selection.
3. Place Table1[CallID] in the rows of a matrix visual
4. Create theses four measures and place them in values of the matrix:
MeasureAttempt = SUMX ( Table1, VAR _AboveSD = Table1[Attempt(s)] - SELECTEDVALUE ( SDTable[Attempt(s)] ) RETURN IF ( _AboveSD >= 0, _AboveSD ) )
MeasureColdTrf = SUMX ( Table1, VAR _AboveSD = Table1[ColdTrf(s)] - SELECTEDVALUE ( SDTable[ColdTrf(s)] ) RETURN IF ( _AboveSD >= 0, _AboveSD ) )
and create MeasureWarmTrf(s) and MeasureConsult(s) following the same logic.
This should show the value above the SD if it's >=0 and blank otherwise plus the sum of each column at the total.
I guess it could also be done in a different way without using the table and invoking your SD measures directly from the measures above.
hi @AlB
Would you know how to apply further filters to this statement if you want to change outcome based on category?
MeasureAttempt = SUMX ( Table1, VAR _AboveSD = Table1[Attempt(s)] - SELECTEDVALUE ( SDTable[Attempt(s)] ) RETURN IF ( _AboveSD >= 0, _AboveSD ) )
so say if Call table looks like this instead (additional In/Out column);
CallID | In/Out | Attempt(s) | ColdTrf(s) | WarmTrf(s) | Consult(s) |
1 | In | 12 | 55 | 22 | 110 |
2 | Out | 199 | 121 | 6 | 92 |
3 | Out | 96 | 79 | 80 | 69 |
4 | In | 16 | 117 | 128 | 45 |
5 | In | 49 | 68 | 43 | 22 |
Ive work out the standard deviation for each permutation, like below;
SD | Attempt_I | Attempt_O | ColdTrf_I | ColdTrf_O | WarmTrf_I | WarmTrf_O | Consult_I | Consult_O |
1 | 7 | 59 | 5 | 60 | 15 | 48 | 14 | 55 |
2 | 13 | 117 | 87 | 125 | 70 | 92 | 64 | 114 |
This matrix is what im aiming for. As you can see the columns are split by Inbound/Outbound hence I need it to be applied to the calculation also,
Any help would be appreciated. Thanks
Hi @AlB
Thanks for the answer. Question, how do I get the Standard deviations in their own table as they are calculated measures from the main call table?
In your initial post you showed the table below. How did you create it? A simple solution would be to just create a new table through the "Enter data" option. Just copy the values there. Would that be an issue? If so we can attempt other things but you would need to show the code of your SD measures and how you get to the values shown in the table below.
As I said previously, we could also modify the measures posted above so that they invoke your SD measures when required instead of looking up the result in the table. That would probably require some more effort though.
SD | Attempt(s) | ColdTrf(s) | WarmTrf(s) | Consult(s) |
1 | 59 | 60 | 48 | 55 |
2 | 117 | 125 | 92 | 114 |
Unfortunatly I cannot store the measures as hard-coded values, as they are subject to change with refreshes, filters etc.
The SD table in my original post was just a mockup table of what I expected the format of the SD measure outputs to be stored as. This is so I can have just one filter that slices/filters the main callID table. (eg when selected SD1, then sum up each column based on each of their respective SD1 value, etc)
With regards to the SD measures, I have a calculated measure per column, so for Attempt looks like this;
SD1:
Attempt_StandardDeviation = CALCULATE(STDEV.P(CallTable[Attempt]),ALL(CallTable))
SD2:
Attempt_StandardDeviation2 = [Attempt_StandardDeviation]*2
And the same logic for cold, warm, consult.
The idea is that I want a matrix/table where Im only showing a sum of each column(attemp, cold, warm, consult) for values that sit above the SD value selected in slicer/filter.
I hope that makes sense,
Try something like this:
SDTable = ADDCOLUMNS ( SELECTCOLUMNS ( { 1, 2 }, "SD", [Value] ), "Attempt(s)", SWITCH ( [SD], 1, [Attempt_StandardDeviation], 2, [Attempt_StandardDeviation2] )
//The other three columns here, following the same pattern )
You can add the remaining columns in the same way, invoking the corresponding measures.
This table should be updated when you refresh the data.
Brilliant, this appears to be what I am after. Just one more question, if I wanted a buttton for "off" on the SD slicer, so that this filter is effectively no longer slicing the data, how can I acheive this? I know that if i select both 1 and 2 this gives me all but I want the filter to have 3 distinct selections so the slicer is clearer to understand.
So it basically appears as "Off", "1", or "2"
Thanks @AlB
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |