cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nnkaya
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.

 

Thanks for reading and answering me in advanced.

 

Kind Regards

 

PowerBI.png

 

 

 

1 ACCEPTED SOLUTION
nnkaya
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)

View solution in original post

5 REPLIES 5
amitchandak
Super User
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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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?

 

 

nnkaya
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)

View solution in original post

PowerBI-1.png

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.