Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rax99
Helper V
Helper V

How to filter based on calculated measure and sum values above that measure

Consider the following sample table showing durations of each state of a call in seconds for each state (attempt, cold transfer, consults etc);

 

CallIDAttempt(s)ColdTrf(s)WarmTrf(s)Consult(s)
1125522110
2199121692
396798069
41611712845
549684322
61457612
7392890185
8551111749
9771898171
1015417114797
11418139146
12678162112

 

 

I have measures that work out the first and second standard deviations for each column;

 

SDAttempt(s)ColdTrf(s)WarmTrf(s)Consult(s)
159604855
211712592114

 

 

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

 

CallIDAttempt(s)ColdTrf(s)WarmTrf(s)Consult(s)
1125522110 - 55
2199 - 59121 - 60 692 -55
396 -5979 -60 80 - 4869 - 55
416117 -60 128 -4845
54968 -604322
6145 -59 761 -482
7392890 -48185 -55 
85511117 -48 49
977 - 591898 -48171 -55
10154 - 59171 - 60147 -4897-55
114181 -6039146 -55
1267 - 598162 -48112 - 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)
384377457542

 

In summary im only interested in seconds above the selected SD filter.

 

 

Any help/guidence will be apprecieated.

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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.      

View solution in original post

7 REPLIES 7
AlB
Super User
Super User

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);

 

CallIDIn/OutAttempt(s)ColdTrf(s)WarmTrf(s)Consult(s)
1In125522110
2Out199121692
3Out96798069
4In1611712845
5In49684322

 

Ive work out the standard deviation for each permutation, like below;

 

SDAttempt_IAttempt_OColdTrf_IColdTrf_OWarmTrf_IWarmTrf_OConsult_IConsult_O
175956015481455
21311787125709264114

 

 

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,

 

Matrix1.JPG

 

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?

@rax99

 

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.     

 

 

SDAttempt(s)ColdTrf(s)WarmTrf(s)Consult(s)
159604855
211712592114

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,

 

@rax99

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.