Forum Discussion
Cost analysis - SAMEPERIODLASTYEAR
I am losing sleep over this one.. Any help is appreciated!
I need help with an expression in power BI DAX measure.
I have two tables with the following relationship. 'Dim_Time'[Date] 1 -> * Fact_KSD_Logistics[Estimated time of departure]
In the table Fact_KSD_Logistics I have the following columns I want to use for an analysis
Values 1.
'Fact_KSD_Logistics'[Amount NOK Finance]
2. 'Fact_KSD_Logistics'[Total net weight]
Dimensions Rows:
1. 'Fact_KSD_Logistics'[From city]
2. 'Fact_KSD_Logistics'[To city]
Columns: 1. 'Dim_time'[Year]
I have the following expression. On the total for each row I get one value but on the details sum I get another.
LY CM Cost / weight = CALCULATE(sum(Fact_KSD_Logistics[Amount NOK Finance])/(sum(Fact_KSD_Logistics[Total net weight])/1000);SAMEPERIODLASTYEAR(Dim_Time[Date]))*(sumx(Fact_Distinct_KSD_Logistics;Fact_KSD_Logistics[Sum of Total net weight])/1000)
The total I want should be 10168251 + 10819679 = 20987930
Instead I am getting 34107 * 759 = 25901236
| From city | To city | Year | Actual cost | CM Actual Cost LY | CM Net Weight LY | Total net weight | CM Cost / MT LY | CM LY Price effect |
| STRAUMEN | BALTIMORE | 2023 | 5 757 223 | 16 579 098 | 5 046 | 3 094 | 3 286 | 10 168 251 |
| STRAUMEN | LYON | 2023 | 5 829 634 | 10 832 249 | 31 049 | 31 013 | 349 | 10 819 679 |
| Totals | 27 411 347 | 36 095 | 34 107 | 759 | 25 901 236 | |||
| Totals I want | 27411347 | 36095 | 34106,5 | 759 | 20 987 930 |
Hi Anonymous ,
Try this measure.
Measure = var a=ADDCOLUMNS(CROSSJOIN(VALUES(Table1[To city]),VALUES(Table2[Date].[Year])),"b",[Cost Last Year Weight This Year]) return SUMX(FILTER(a,[To city] in VALUES(Table1[To city])&&[Date].[Year] in VALUES(Table2[Date].[Year])),[b])Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Anonymous ,
You can create a new measure to be placed on the visual object instead of the old one.
NewMeasure = SUMX(VALUES('Dim_time'[Year]), [CM LY Price effect])If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I was getting HTML-errors trying to upload the tables.
Seems like that has been an ongoing issue, so I uploaded them to drive and added the link underneath.
If we look at the table for 2023, the table total for Cost Last Year Weight This Year is 334 while the desired result should be 331 which would be the aggregation of the result for every row.
While 334 is aggregating each measure before making the calculation.Hi Anonymous ,
Try this measure.
Measure = var a=ADDCOLUMNS(CROSSJOIN(VALUES(Table1[To city]),VALUES(Table2[Date].[Year])),"b",[Cost Last Year Weight This Year]) return SUMX(FILTER(a,[To city] in VALUES(Table1[To city])&&[Date].[Year] in VALUES(Table2[Date].[Year])),[b])Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Neeko Tang