Forum Discussion

LorenzoC's avatar
LorenzoC
Regular Visitor
2 years ago
Solved

Measure does not work properly

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/20...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

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

     

     

lbendlin's avatar
lbendlin
Super User
2 years ago

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's avatar
LorenzoC
Regular Visitor
2 years ago

Dear lbendlin ,

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

IdDateS_CostD_CostTot_D_CostDESIRED RESULT
101/01/202450015003300646,5517241
101/01/202450018003300775,862069
103/01/20242000250025001077,586207
201/02/202450021002100692,3076923
202/02/202450028002800923,0769231
210/02/20242000420042001384,615385

 

This however does not change my desired result.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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