Need help to make formula work (filter)

[Hawjeen]

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)

Level	Comission
0	0
5000	100
10000	250

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 

[gooranga1]

You could possibly try the below.

[MFelix · Accepted solution]

Hi @Hawjeen ,

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.

[Hawjeen]

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]

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?

[Hawjeen]

Hi,

Yes you are correct, i will after divide it by a simple countrows.

i have a small data sample, but i don’t know how to upload it to you?

but the general idea is that it will look for a document number, in a payment file. if the document number is there and it has the payment type 26 it will sum all of them together

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

afterwards instead of the amount I want to have the commission, based on the result the formula gives me and it is in a another table:

Interval	Comision
0	0
2000	50
5000	100
10000	150
20000	200

The last part is where I need the help.

Expected result is, any amount from the first sumif between 0-1999 should be 0, everything between 5000-9999 should be 1000 a.o.

Hope it all make sense, or else i can email you the data sample.

Regards

Hawjeen 

[MFelix]

You can  share the sample data by onedrive, google drive or wetransfer link.

[Hawjeen]

here is the link: https://we.tl/t-dt933ZIkGA

I really apreciate your help! i'm new into the powerbi world and having a hard time transtiting from excel

[Hawjeen]

Hi again,

After hours of trying, i finally figured out a simple method with Lostnonblank formula.

Once again, thank you for trying. im sure the 1st formula was right, i just could not get it to work.

Regards 

Hawjeen