Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYou could possibly try the below.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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.
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?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
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
You can share the sample data by onedrive, google drive or wetransfer link.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshere 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |