Forum Discussion
SUM Column in table
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
5 Replies
- amitchandak
Super User
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 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.
