Forum Discussion
Mileage Calculation
See if this works.
The model
The measures
Mileage = SUM ( 'Dataset 1'[Distance (Miles)] )Daily Commute = SUM('Dataset 2'[Daily Commute (Miles)])RegDaily Commute = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Dataset 1', 'Dataset 1'[Start Date], 'Dataset 2'[Vehicle Reg] ), "DC", [Daily Commute] ), [DC] )Net Mileage = [Mileage] - [RegDaily Commute]to get..
I've attached the sample PBIX file
Example below
Dataset 1
| Vehicle Reg | Start Date | End Date | Distance (Miles) |
| 123 | 01/01/2021 | 01/01/2021 | 10 |
| 123 | 01/01/2021 | 01/01/2021 | 15 |
| 123 | 02/01/2021 | 02/01/2021 | 20 |
| 123 | 02/01/2021 | 02/01/2021 | 30 |
Dataset 2
| Vehicle Reg | Daily Commute (Miles) |
| 123 | 3 |
| 124 | 2 |
| 125 | 4 |
Sum would be as follows
Sum of Distance Miles in dataset 1 = 75
75 - Daily Commute miles for every day mentioned in dataset 1 = 69
For every vehicle reg in dataset 1 there will be a the same vehicle reg in dataset 2 along with its daily commute mileage.
See if this works.
The model
The measures
Mileage =
SUM ( 'Dataset 1'[Distance (Miles)] )
Daily Commute = SUM('Dataset 2'[Daily Commute (Miles)])RegDaily Commute =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( 'Dataset 1', 'Dataset 1'[Start Date], 'Dataset 2'[Vehicle Reg] ),
"DC", [Daily Commute]
),
[DC]
)
Net Mileage =
[Mileage] - [RegDaily Commute]
to get..
I've attached the sample PBIX file
Thats spot on!
Thank you for taking the time in your day to help me!
Elliot
Hi Paul,
After putting the formula to the test with real data I am getting a strange result.
The formula is working correct as seen in the below table screenshot (some vehicles will not have daily commute miles allocated) but for some reason the sum of all RegDailyCommute is alot higher than all of the values added together.
Any ideas?
Anonymous
I'm not sure what's going on at your end. I've just added a new reg with no daily commute and the measures work.
Can you post the code you are using for the [RegDaily commute] measure?
Hi,
I have changed the relationship settings to 'both ways' this seems to have solved it!
Many thanks,
Elliot