Forum Discussion

elaj's avatar
elaj
Helper IV
6 years ago

Calculate wierd filter behavior

Hi,

 

i have to show typical weighted survey answer(category) percentages.

I thought i know how to do it and now i get a really confusing result.

 

Ok i try to explain it:

 

I have one column with the weight, one column with the answers (Q1_lbl) as "strings" which i want to use as a filter for the percentage measure (Q1 % (W)), to show the percentage of each answer(category). I have one additional column for the sort order for the labels (Q1_lbl_sort).

 

 

I need an unweighted "base" to show to my viewers and normally i use this approach:

 
New Measure:
Q1 % B (UW) = calculate(COUNTA(data[Q1_lbl]), data[Q1_lbl] <> Blank(), all(data[Q1_lbl]))
why data[Q1_lbl] <> BLANK()? because i only want to calculate the persons who have answers.
why "all"? because i want to filter the values by Q1_lbl. The base has to be the same, because for all answers i had the same base.
 
Now i need a weighted base to calculate my percentage and normally i use this approach:
 
New Measure:
Q1 % B (W) = calculate(SUM(data[weight]), data[Q1_lbl] <> Blank(), all(data[Q1_lbl]))

why data[Q1_lbl] <> BLANK()? because i only want to calculate the persons who have answers.

why "all"? because i want to filter the values by Q1_lbl. The base has to be the same, because for all answers i had the same base.

 

Now i need the weighted mentions of my probands. its the same as my weighted base but without the "all" statement, because now i want to have it filtered by my answer(categories) Q1_lbl and normally i use this approach:

 

New Measure:

Q1 % N (W) = calculate(SUM(data[weight]), data[Q1_lbl] <> BLANK())

why data[Q1_lbl] <> BLANK()? because i only want to calculate the persons who have answers.

 

calculating the percentage should be easy now:

 

New Measure:

Q1 % (W) = [Q1 % N (W)] / [Q1 % B (W)]

 

and now i have a problem. the mentions measure [Q1 % N (W)] (coloured orange in the picture) has the same values in each row. I think it is because i used data[Q1_lbl] <> BLANK(). But i have to use this.. i only want to calculate the people who have an answer in Q1_lbl. And i thought only all(data[Q1_lbl]) should delete filters for this column.

 

 

so the first question is:

how can i calculate only people who have answers in Q1_lbl BUT dont destroy the filter of Q1_lbl in the visual?

And how can i filter this to not create values in the red marked row? (without filtering the visual)

 

And the other weird thing is.. if i now sort Q1_lbl after Q1_lbl_sort. then everything gets filtered after Q1_lbl.

But also not as it should. Row 1 is gone... this is good.. And [Q1 % N (W)] now has the right values. But the two base columns should have the values as in picture one.

 

 

Do you have an idea how to solve this? Or am i doing it wrong? is there a better approach?

 

is there a way to filter something over a column in a calculate statement without destroying additional filters added in the visual out of the same column?

 

here my file

attached_pbx_file 

 

thanks for your answers

 

Need Help

6 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    use KEEPFILTERS in your code, even while you think you are removing blank, when you are at the blank row you are returning same result because data[Q1_lbl] <> BLANK () expands into FILTER ( ALL ( data[Q1_lbl] ), data[Q1_lbl] <> BLANK () ) and then even at the blank cell you are returning all the rows in data[Q1_lbl] that are not blank but with KEEPFILTERS you create an INTERSECTION between the original filter and the new filter create by CALCULATE.
    Also for future reference you don't need to use ALL in such calculation because there already is a hidden ALL in data[Q1_lbl] so 

    data[Q1_lbl] <> BLANK (), ALL(data[Q1_lbl] ) is equal to data[Q1_lbl] <> BLANK () for this case.
    Q1 % N (W) =
CALCULATE ( SUM ( data[weight] ), KEEPFILTERS ( data[Q1_lbl] <> BLANK () ) )

     now just use KEEPFILTERS  in all measures and use DIVIDE instead of division operation.

    Q1 % (W) = DIVIDE([Q1 % N (W)], [Q1 % B (W)])

     

    • elaj's avatar
      elaj
      Helper IV
      6 years ago

      Hi AntrikshSharma ,

       

      thanks for the fast reply.

       

      keepfilters works fine for my mentions measure. this is great.

      but for my base calculation i need the sum of all mentions.. so if i use keepfilters on them, they will be filtered... and once again i have 100% as a result. which is wrong.

       

      but if i do it like my old approach i will have values in the first row:

       

      so basically i dont want values in the first row.

       

      and second:

      when i now sort Q1_lbl after Q1_lbl_sort i have the other issue that my values go crazy:

       

      how to solve this? why does the sorting destroy everything? is this a bug?

       

      this is my desired result:

       

       

      thanks for your answers 🙂

       

      corrected_pbix 

      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        6 years ago

        This is first time I have seen an issue because of the sort order. Now this is hurting even my head too!!