Measure to calculate project volume

[Chub1]

Hello,

I need to create a measure, that calculates the project volume (yearly quantity over several years) for each ID.

My Data structure looks like this:

So for the first ID, the expected result would be 25.000 (10.000 in 2023 and 15.000 in 2024).

For the second ID, the result should be 30k + 40k + 50k = 120k.

The yearly qty does not change based one freight mode or lot sizes, so its just depending on the year.

However the first/starting year can be different for every ID, also the count of years can be 1 or up to 5 years.

On top of that, there is a special case with scenarios (e.g. ID 3). In this case, the result should be only the first scenario, so 100k in the example above.

On top of that, I need a measure that calculates the min sales price for each year (independent from freight mode or lot size). So result for ID 1 would be 3 for 2023 and 2 for 2024.

However for the scenario case, the result should be the sales price corresponding to the qty of the first scenario (so price 3 for 100k in example).

ID	Freight Mode	Lot Size	Year	Yearly Quantity	Sales Price
1	Sea	1.000	2023	10.000	5
1	Sea	2.500	2023	10.000	4
1	Sea	5.000	2023	10.000	3
1	Air	500	2023	10.000	6
1	Air	1.000	2023	10.000	5
1	Air	1.500	2023	10.000	4
1	Sea	1.000	2024	15.000	4
1	Sea	2.500	2024	15.000	3
1	Sea	5.000	2024	15.000	2
1	Air	500	2024	15.000	5
1	Air	1.000	2024	15.000	4
1	Air	1.500	2024	15.000	3
2	Truck	3.000	2024	30.000	10
2	Truck	10.000	2024	30.000	9
2	Air	2.000	2024	30.000	12
2	Air	4.000	2024	30.000	11
2	Truck	3.000	2025	40.000	9,5
2	Truck	10.000	2025	40.000	8,7
2	Air	2.000	2025	40.000	11
2	Air	4.000	2025	40.000	10
2	Truck	3.000	2026	50.000	9,5
2	Truck	10.000	2026	50.000	8,7
2	Air	2.000	2026	50.000	11
2	Air	4.000	2026	50.000	10
3	Sea	15.000	Scenario 1	100.000	3
3	Sea	15.000	Scenario 2	150.000	2
3	Sea	15.000	Scenario 3	200.000	1

Thanks a lot in advance, appreciate the suggestions and help.

[rubayatyasmin]

Hi, @Chub1 

this is what i could achieve. 

when applied filter:

for id 3

Measures for the above 

ProjectVolume1 =

SUMX (

    SUMMARIZE (

        FreightTable1,

        FreightTable1[ID],

        FreightTable1[Year],

        "FirstYearlyQuantity", MIN ( FreightTable1[Yearly Quantity] )

    ),

    [FirstYearlyQuantity]

)

 //////////

MinSalesPrice =

CALCULATE(

    MIN(FreightTable1[Sales Price]),

    ALLEXCEPT(FreightTable1, FreightTable1[Year])

)

////

Scenario1Quantity =

CALCULATE(

    SUM(FreightTable1[Yearly Quantity]),

    FILTER(FreightTable1, FreightTable1[ID] = 3 && FreightTable1[Year] = "Scenario 1")

)

this should help or give you an idea of how to achieve your goal.