topic Measure does not work properly in DAX Commands and Tips

Measure does not work properly

LorenzoC — Sun, 16 Jun 2024 09:01:10 GMT

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

Re: Measure does not work properly

lbendlin — Sun, 16 Jun 2024 23:56:45 GMT

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.

Re: Measure does not work properly

LorenzoC — Mon, 17 Jun 2024 06:29:27 GMT

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.

Re: Measure does not work properly

lbendlin — Thu, 20 Jun 2024 23:52:58 GMT

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