Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help to make formula work (filter)

Hi,

 

Im not sure what i'm droing wrong, hope that someone is able to help me fix it.

What i'm trying to do is to return a value that is between a range eg. Store[Amount]=4500 the return value should be 0, and Store[Amount]=15000 return should be 250. (similar ta a vlookup in excel with a approximate result)

 

LevelComission
00
5000100
10000250

 

This is the formula i'm trying to solve, but right now i only get returned a value if the amount is ecxatly 5000 or 10000.

 

 

Comission = 
CALCULATE(
    FIRSTNONBLANK(Bank[comission];1);
    FILTER(
        Bank;
        Bank[Interval]<=FORMAT(Store[Amount];"#")
            && Bank[Interval]>=FORMAT(Store[Amount];"#")
    )
)

 

 

Best regards

Hawjeen 

 

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    Try the followin code:

    Comission =
CALCULATE (
    MINX (
        FILTER (
            Bank;
            Bank[Interval] <= FORMAT ( Store[Amount]; "#" )
                && Bank[Interval] >= FORMAT ( Store[Amount]; "#" )
        );
        Bank[Comission]
    )
)

    Be aware that since you don't have values above 10.000 on the comissions will return blank, you can add a level with 9999999999 and a 0 value or another one that you think is correct.

     

     

8 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    Try the followin code:

    Comission =
CALCULATE (
    MINX (
        FILTER (
            Bank;
            Bank[Interval] <= FORMAT ( Store[Amount]; "#" )
                && Bank[Interval] >= FORMAT ( Store[Amount]; "#" )
        );
        Bank[Comission]
    )
)

    Be aware that since you don't have values above 10.000 on the comissions will return blank, you can add a level with 9999999999 and a 0 value or another one that you think is correct.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you, i think it works, but i think my data structure is a bit flawed.

      My [amount] is a formula and i cant use it in the formula you've created for me.

       

      the formula to calculate [amount]:

      calculate
(sum
(data[Amount Tendered]);
FILTER(
FILTER(
data;data[Receipt No_]=
EARLIER(store[DocumentNo]));
data[Tender Type]="26"))

       

      basically a sumif function but i get the error that the value has to many possibilities and therefor cant execute.

       

       

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        What is the type of calculation you are making here?

         

        In the syntax appear to me that you are calculating full value with some specific filters so for a specific receipt number all rows will show the same number is this correct?

         

        You can make a measure to use on the other measure.

         

        can you share a small sample data and expected result?