Forum Discussion

LorenzoC's avatar
LorenzoC
Regular Visitor
2 years ago
Solved

Measure does not work properly

Dear All,

i have the following dataset:

IdDateS_CostD_CostTot_D_Cost
101/01/202450015003300
101/01/202450018003300
103/01/2024200025002500
201/02/202450021003300
202/02/202450028003300
210/02/2024200042002500

 

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:
IdDateS_CostD_CostTot_D_CostDESIRED RESULT
101/01/202450015003300646,5517241
101/01/202450018003300775,862069
103/01/20242000250025001077,586207
201/02/202450021003300692,3076923
202/02/202450028003300923,0769231
210/02/20242000420025001384,615385

 

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

Lorenzo

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]
				)
			)
		)

     

     

3 Replies

  • 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]
				)
			)
		)