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 sho...
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.

     

     

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?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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:

     

    IntervalComision
    00
    200050

    5000

    		100
    10000150
    20000200

     

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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