Forum Discussion
Solved
Average (excluding other columns)
I have data by diff carriers and expenses are coming from diff dates and diff currencies.
I would like to see the ratio of monthly expenses against the average expense (total expenses by carrier and divide the months)
I tried the measure below, but it's calculated average by days instead of month.
avg exp by carrier =
CALCULATE(
AVERAGE('Sample Data'[exp_usd])
,FILTER(
ALLSELECTED('Sample Data'),
'Sample Data'[Carrier Name]=MAX('Sample Data'[Carrier Name])))
Data:
| Carrier Name | Date | Currency | Exp | FX | USD |
| UPS | 1/1/2023 | USD | 5 | 1 | 5 |
| FedEx | 1/2/2023 | USD | 6 | 1 | 6 |
| UPS | 1/2/2023 | GBP | 6 | 1.2 | 7.2 |
| FedEx | 1/4/2023 | USD | 7 | 1 | 7 |
| UPS | 2/1/2023 | USD | 8 | 1 | 8 |
| FedEx | 2/1/2023 | EUR | 8 | 1.1 | 8.8 |
| UPS | 2/2/2023 | USD | 9 | 1 | 9 |
| FedEx | 3/3/2023 | USD | 10 | 1 | 10 |
| UPS | 3/11/2023 | EUR | 11 | 1.1 | 12.1 |
| FedEx | 3/11/2023 | USD | 11 | 1 | 11 |
| UPS | 3/31/2023 | USD | 12 | 1 | 12 |
| FedEx | 3/31/2023 | USD | 13 | 1 | 13 |
| UPS | 4/1/2023 | USD | 14 | 1 | 14 |
| FedEx | 4/5/2023 | USD | 15 | 1 | 15 |
| UPS | 4/8/2023 | USD | 16 | 1 | 16 |
| FedEx | 4/8/2023 | GBP | 16 | 1.2 | 19.2 |
| UPS | 4/8/2023 | USD | 17 | 1 | 17 |
| FedEx | 5/1/2023 | USD | 18 | 1 | 18 |
| UPS | 5/5/2023 | USD | 19 | 1 | 19 |
| FedEx | 6/9/2023 | USD | 20 | 1 | 20 |
| UPS | 6/15/2023 | USD | 21 | 1 | 21 |
Hi,
Please find attached my PBi file.
Hope this helps.
1 Reply
Hi,
Please find attached my PBi file.
Hope this helps.