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

     

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.

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

        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?