Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

@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
Not applicable

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
Not applicable

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
Not applicable

PowerBI-1.png

It gives an error.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.