Get sum of a column - filtered by groups/dimensions in other columns

[tims__]

Dear community,

I need some help with a calculation, where I want to find the sum of the "AMOUNT" column, but considering only my dimensions ID, SCENARIO and YEAR (and every combination of them) and not taking into account the different products in the corresponding column. So you have to read the table in a way that the amounts are only connected to an ID, in a specific scenario, in a specific year, but not to the products, that are only "doubling" the lines - as you can see from the amounts every two lines in this example.

Unfortunately, the scenarios, years and products can change, e.g. another ID may not have ProductA, another ID may not have a year 2023. So you can't filter by specific strings or values or just divide by 2. 🙂

This is the table:

ID	SCENARIO	YEAR	PRODUCT	AMOUNT
1	S1	2022	ProductA	70
1	S1	2022	ProductB	70
1	S1	2023	ProductA	80
1	S1	2023	ProductB	80
1	S2	2022	ProductA	60
1	S2	2022	ProductB	60
1	S2	2023	ProductA	65
1	S2	2023	ProductB	65
2	S1	2022	ProductA	50
2	S1	2022	ProductB	50
2	S1	2023	ProductA	30
2	S1	2023	ProductB	30
2	S2	2022	ProductA	60
2	S2	2022	ProductB	60
2	S2	2023	ProductA	20
2	S2	2023	ProductB	20

I have been playing around a lot with FIRSTNONBLANK, ALLEXCEPT, FILTER, EARLIER and variables but did not find a proper solution. So I would be very glad if you could help me with this issue.

Thanks in advance

Tim

[Greg_Deckler]

@tims__ So, as a column, maybe:

Column = 
  VAR __ID = [ID]
  VAR __Scenario = [SCENARIO]
  VAR __Year = [YEAR]
  VAR __Table = FILTER(ALL('Table'),[ID] = __ID && [SCENARIO] = __Scenario && [YEAR] = __Year) 
RETURN
  SUMX(__Table,[AMOUNT])

If you want a measure, just wrap the first three VAR's with a MAX like VAR __ID = MAX('Table'[ID])

[tims__]

Dear  @Greg_Deckler , thanks a lot for your reply and taking time to solve this!

Unfortunately, the code does not provide a solution, as the results in the column are the double of the (already) double, see table.

So for the first two rows I would only need (once) the Amount of 70. Also, if there would be a "Product C", I would still only need (the sum) 70 as a result for all rows that have this same combination of my dimensions (ID, Scenario, Year). So, for a new column in this example, a possible outcome would be: [70, blank, 80, blank, 60, blank ...]. Just a measure with the final result would also be fine.

Do you have a solution for this? Thanks in advance!

Tim

IDSCENARIOYEARPRODUCTAMOUNTVeh_Amount

1	S1	2022	ProductA	70	140
1	S1	2022	ProductB	70	140
1	S1	2023	ProductA	80	160
1	S1	2023	ProductB	80	160
1	S2	2022	ProductA	60	120
1	S2	2022	ProductB	60	120
1	S2	2023	ProductA	65	130
1	S2	2023	ProductB	65	130
2	S1	2022	ProductA	50	100
2	S1	2022	ProductB	50	100
2	S1	2023	ProductA	30	60
2	S1	2023	ProductB	30	60
2	S2	2022	ProductA	60	120
2	S2	2022	ProductB	60	120
2	S2	2023	ProductA	20	40
2	S2	2023	ProductB	20	40

[Greg_Deckler]

@tims__ OK, how about this?

Measure = 
  VAR __Table = SUMMARIZE('Table',[ID],[SCENARIO],[YEAR],"__Value", AVERAGE([AMOUNT]))
RETURN
  SUMX(__Table,[__Value])