SUM Column in table

[Anonymous]

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

[amitchandak]

@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

[Anonymous]

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?

[Anonymous · Accepted 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]

It gives an error.

[Anonymous]

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.