Forum Discussion

Tommyvhod's avatar
Tommyvhod
Icon for Helper II rankHelper II
6 years ago
Solved

Issue with meaure total

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's avatar
    Anonymous
    6 years ago

    Hi Tommyvhod ,

     

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

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

     

6 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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's avatar
      Tommyvhod
      Icon for Helper II rankHelper II
      6 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Tommyvhod ,

     

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

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

     

    • Tommyvhod's avatar
      Tommyvhod
      Icon for Helper II rankHelper II
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.