Forum Discussion

ChristianHE's avatar
ChristianHE
Frequent Visitor
5 years ago
Solved

Using Variables as filters

Hi community

 

I am having a little trouble understanding how to use variables, i have made the following formula:

 

Test = 

Var Rep = VALUES(Kundetabel[Repræsentant])
Var Kunde = VALUES(Kundetabel[KUNDENRNAVN])
 
return
CALCULATE([Potientiale];
TOPN(10;
CALCULATETABLE(all(Kundetabel);
FILTER(Kundetabel; Kundetabel[Repræsentant] in Rep)
)
;[Potientiale];ASC)
;Rep; Kunde
)
 
I was hoping my calculatetable would give me a table consisting only of the chosen "Repræsentant", but i suspect it does not. Can someone explain me how it works, alternately see what is wrong with my formula?
Calculatetable filter
TopN
variable

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    ChristianHE , Try like

     

    Test = CALCULATE([Potientiale];TOPN(10,allselected(Kundetabel);[Potientiale],ASC),VALUES(Kundetabel[KUNDENRNAVN]))

    • ChristianHE's avatar
      ChristianHE
      Frequent Visitor
      5 years ago

      amitchandak  it works, Thank you very much.

       

      If i wanted to add more filters to my [Kundetabel], for example if i only wanted to see customers with a sales above 1.  Would that be possible to add in the formula or should it be added in the "Filters"-pane?

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        ChristianHE , better have to measure with filter sales >1, if it line level. If it after grouping then use values 

         

        sumx(Filter(values(Table[Col1]),[sales]>1),[Sales])