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.

     

     

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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