Issue with meaure total

[Tommyvhod]

Hi All

 I created a measure to make a weighted average in production. The formula works but the total of measure is not correct.

I have found multiple topics about this but I was unable to modify the formula based on those samples.

The issue is that all work stations has multiple part IDs , I divided the total times with the distinct IDs ( or if the final work station has more IDs like the actual work station than with that.)

It is OK for each work stations ( Operacia) , they seems to have 160 distinct IDs maximum, but in total it counts 220 IDs trough all work stations and divides the total time with that.

The goal would be to sum each work stations result( yellow) and not the last all time / all distinct IDs ( red)

I hope you understand it

Dax:

allsum = VAR _Max = MAXX(ALLSELECTED(Data);Data[Operacia])

return

DIVIDE(SUM(Data[Act time]);

IF(CALCULATE(DISTINCTCOUNT(Data[ID]);Data[Operacia]) >CALCULATE(DISTINCTCOUNT(Data[ID]);Data[Operacia] = _Max);CALCULATE(DISTINCTCOUNT(Data[ID]);Data[Operacia]);CALCULATE(DISTINCTCOUNT(Data[ID]);Data[Operacia] = _Max)

))

[Anonymous · Accepted solution]

Hi @Tommyvhod ,

Below one may work for you, but it might be a bit slow

Sum = 
	SUMX(
		KEEPFILTERS(Values(Data[Operacia]))
	,
		CALCULATE([allsum])
	)

[Tommyvhod]

I added this one as well, but it seems to have the same result as [allsum] 7.40

[Anonymous]

Hi @Tommyvhod ,

Sorry did not notice that this is baiscally weitghted average.

If the division is Allsum then it should look like that.

VAR __CATEGORY_VALUES = VALUES('Data'[Operacia])
RETURN
	DIVIDE(
		SUMX(
			KEEPFILTERS(__CATEGORY_VALUES),
			CALCULATE([Count of IDs] * [allsum])
		),
		SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE([allsum]))
	)

 I am not sure if this is an expected result so if there is a need to have it other way around...

VAR __CATEGORY_VALUES = VALUES('Data'[Operacia])
RETURN
	DIVIDE(
		SUMX(
			KEEPFILTERS(__CATEGORY_VALUES),
			CALCULATE([allsum] * [Count of IDs])
		),
		SUMX(
			KEEPFILTERS(__CATEGORY_VALUES),
			CALCULATE([Count of IDs])
		)
	)

Since you may need to refine it... so what "SUMX(KEEPFILTERS(VALUES([Column])),Calculate([Measure])) does... basicaly it pre-calculates values per specified column and sums results up, not relying on Total values.  so [allsum]*[Count of IDs] here will get result for each "Operatia" first and then sum up results from individual calculation.

Rest is just placing proper numerator, denominator to get the results.

[Tommyvhod]

I added both formulas. I was unsure about how you got the count of IDs so I replaced it with distinctcount(Data[ID])

The result for the first is 0.2927

fr the second there is an error message.

Maybe the replacement of count of ids is wrong?

[sturlaws]

Hi @Tommyvhod, can you to create a new measure like this:

Allsum Final =
IF ( HASONEVALUE ( Data[Operacia] ); [allsum]; SUMX ( data; [allsum] ) )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too. 

[Tommyvhod]

I added the formula, indeed i tryed similar one earlier. But I get a 1,63K final result. The reality should be around 10.5h