cancel
Showing results for
Did you mean:
Regular Visitor

## 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.

Kind Regards

1 ACCEPTED SOLUTION
Regular Visitor

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)

5 REPLIES 5
Super User

@nnkaya , 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

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Regular Visitor

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?

Regular Visitor

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)

Regular Visitor

It gives an error.

Regular Visitor

Hello @amitchandak ,

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!