Measure does not work properly

[LorenzoC]

Dear All,

i have the following dataset:

Id	Date	S_Cost	D_Cost	Tot_D_Cost
1	01/01/2024	500	1500	3300
1	01/01/2024	500	1800	3300
1	03/01/2024	2000	2500	2500
2	01/02/2024	500	2100	3300
2	02/02/2024	500	2800	3300
2	10/02/2024	2000	4200	2500

Wha I need is to define a MEASURE (if impossible through a measure, a calculated column is also accepted) which splits the value of TOTAL DISTINCT S_Cost with reference to the Id and Date.

Firstly I created two measures for defining the distinct S_cost and Total_D_Cost: 

DISTINCT_S_Cost =

VAR summarizetable = SUMMARIZE(

    'Tabella 3 (Foglio2)',

    'Tabella 3 (Foglio2)'[Id],

    'Tabella 3 (Foglio2)'[S_Cost]

)

RETURN SUMX(summarizetable, [S_Cost])

and 

DISTINCT_TOTAL_D_Cost = 

VAR summarizetable = SUMMARIZE(

    'Tabella 3 (Foglio2)',

    'Tabella 3 (Foglio2)'[Id],

    'Tabella 3 (Foglio2)'[Tot_D_Cost]

)

RETURN SUMX(summarizetable, [Tot_D_Cost])

Then I created my FINAL MEASURE, which was supposed to give my desired result:

Final_Measure = DIVIDE(SUM('Tabella 3 (Foglio2)'[D_Cost]),[DISTINCT_TOTAL_D_Cost])* [DISTINCT_S_Cost]

However, this does not work. This is what it returns:

Instead, my desired result would be the following:

Id	Date	S_Cost	D_Cost	Tot_D_Cost	DESIRED RESULT
1	01/01/2024	500	1500	3300	646,5517241
1	01/01/2024	500	1800	3300	775,862069
1	03/01/2024	2000	2500	2500	1077,586207
2	01/02/2024	500	2100	3300	692,3076923
2	02/02/2024	500	2800	3300	923,0769231
2	10/02/2024	2000	4200	2500	1384,615385

Do you think I can adjust this final measure to reach my desired result?
Thank you a lot

Lorenzo

[lbendlin]

for your last row why is Tot_D_Cost lower than D_Cost?

What is the unique identifier for each row?  If you don't provide one then you cannot use measures as Power BI automatically aggregates.

[LorenzoC]

Dear @lbendlin ,

you're right. The correct table is the following:

Id	Date	S_Cost	D_Cost	Tot_D_Cost	DESIRED RESULT
1	01/01/2024	500	1500	3300	646,5517241
1	01/01/2024	500	1800	3300	775,862069
1	03/01/2024	2000	2500	2500	1077,586207
2	01/02/2024	500	2100	2100	692,3076923
2	02/02/2024	500	2800	2800	923,0769231
2	10/02/2024	2000	4200	4200	1384,615385

This however does not change my desired result.

[lbendlin · Accepted solution]

Result = DIVIDE(
			SUM('Table'[D_Cost])
			* SUMX(
				DISTINCT(ALLSELECTED('Table'[S_Cost])),
				[S_Cost]
			),
			CALCULATE(
				SUM('Table'[D_Cost]),
				ALLEXCEPT(
					'Table',
					'Table'[Id]
				)
			)
		)