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.
I have a table that displays data from SharePoint list.
The table has several columns as you can see at the screenshot below.
One of them is: Mileage
This is the question:
Every month I want to find out how much do i pay each user based on their total milages.
The argument is this:
If the total mileage is equal or less than 833 miles, the the payment will be= 0.45*Total Milegae.
If the total mileage is greater than 833 miles, the the payment will be = 833*0.45+(Total Mileage-833)*0.25.
For eaxmple: If an emplyee has 933 miles in total for September.
Then s/he will be paid:
833*0.45=£374.85
933-833=100*0.25=£25
Total s/he gets=£374.85+£25=£399.85
How can I be able to show this new measure in Power BI.
Thanks for reading and answering me in advanced.
Kind Regards
Solved! Go to Solution.
I sorted now:
Measure = if(
SUMX('Mileage Log','Mileage Log'[Mileage])<=833,
SUMX('Mileage Log','Mileage Log'[Mileage])*0.45,
(0.45*833)+(SUMX('Mileage Log','Mileage Log'[Mileage])-833)*0.25)
@Anonymous , Try a measure like this
measure =
var _sum = calculate(sum(Table[Total Milegae]))
return
Sumx(values(Table[Month Year]), if(_sum > 833 , (_sum -833)*.25 + 833 *.45, _sum*.45))
you need a month year column
Hello, That formula did not work.
This formula works in PowerApps but I tried in Power BI and it did NOT work.
If(
Sum('Mileage Log',Mileage) <= 833,
Sum('Mileage Log',Mileage)*0.45,
(0.45 *833)+(Sum('Mileage Log',Mileage)-833)*0.25
)
I was wondering if you could find any sloution for me please?
I sorted now:
Measure = if(
SUMX('Mileage Log','Mileage Log'[Mileage])<=833,
SUMX('Mileage Log','Mileage Log'[Mileage])*0.45,
(0.45*833)+(SUMX('Mileage Log','Mileage Log'[Mileage])-833)*0.25)
It gives an error.
Hello @amitchandak ,
Thanks for your quick answer.
Right now, now worries about year and month.
I will filter this from slicer fields above as you can see on the screenshot.
I only need be able to work out the total payment when I filter Month and day:
Let's say: If I filter a year(2021), month(September) then the total millage will be displayed at the bottom of the table and based on that total mileage I need to see the total payment to be displayed too.
So how can I achive that?
Thanks again.
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 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |