Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |