Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LorenzoC
Regular Visitor

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:
 
LorenzoC_0-1718527915752.png
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

1 ACCEPTED SOLUTION

lbendlin_1-1718927401743.png

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

 

 

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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.

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_1-1718927401743.png

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

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.