Calculating weights against the entire year with a multi-year table

[Hadrey]

Hello, i have a problem with claulating weight angist the entire year with multi-yert table
i have 2 tables one with

ID	NAME	PRICE	DATE
1	a	200	2023
2	b	300	2023
1	a	100	2024
1	a	540	2023
1	a	300	2023

and second with dictionary like

id	name	brand	subbrand	factory	status	type
1	a	br1	subbr1	fac1	nc	y
2	b	br1	subbr1	fac1	nc	y
3	c	br2	subbr2	fac1	no	n

i have set relation between these 2 tables beacose i need to filter by name/brand etc.

I need something like this (sum of column weight needed to be 1):

	2023	2024	weight 2023	weight 2024
a	1040	100	0,7761	1
b	300		0,2239

i tired using

weight_2023 = DIVIDE(
 CALCULATE(
  SUM(TABLE1[PRICE]),
  TREATAS(VALUES(Years[YEAR]), TABLE1[DATE]),
  TREATAS(VALUES(DICTIONARY[ID]), TABLE1[ID])
 ),
 CALCULATE(
  SUM(TABLE1[PRICE]),
  TREATAS(VALUE(Years[YEAR]), Table1[DATE]
 )
)

but its only work when relationship is deactivated, but because of this other measures not work

[Anonymous]

Hi @Hadrey ,

Below is my table1:

Below is my table2:

You must create a column:

Column = CALCULATE(SUM('Table'[PRICE]),FILTER('Table','Table'[DATE] = "2023"))

and then you can create two measure:

Weight 2023 = 
  VAR _a = CALCULATE(SUM('Table'[PRICE]),FILTER('Table','Table'[DATE] = "2023"),ALLEXCEPT('Table','Table'[NAME]))
  VAR _b = SELECTEDVALUE('Table'[Column])
  RETURN
  DIVIDE(_a,_b)


Weight 2024 = 
  DIVIDE(CALCULATE(SUM('Table'[PRICE]),FILTER('Table','Table'[DATE] = "2024"),ALLEXCEPT('Table','Table'[NAME])),CALCULATE(SUM('Table'[PRICE]),FILTER('Table','Table'[DATE]="2024")))

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hadrey]

Hi, @Anonymous
First solution (with column) worked fine, but is not fit for my project, becouse I need to calculate weight but only for that what is after filter (setted by viewer by slicers) but in your solution is that when i applied slicer for name and select a then its show only 0,7761 but should show 1

and for second solution I have changed code to follow year 2023 and its showing only 1.0.

this is my `weight 2023`

`WEIGHT-2023`

and relationship on tables