topic Re: Issue with meaure total in DAX Commands and Tips

Issue with meaure total

Tommyvhod — Thu, 30 Jan 2020 09:51:46 GMT

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)

))

Re: Issue with meaure total

sturlaws — Mon, 03 Feb 2020 09:11:16 GMT

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.

Re: Issue with meaure total

Tommyvhod — Tue, 04 Feb 2020 11:41:18 GMT

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

Re: Issue with meaure total

Anonymous — Tue, 04 Feb 2020 12:35:16 GMT

Hi Tommyvhod ,

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

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

Re: Issue with meaure total

Tommyvhod — Tue, 04 Feb 2020 12:38:54 GMT

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

Re: Issue with meaure total

Anonymous — Tue, 04 Feb 2020 13:43:09 GMT

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.

Re: Issue with meaure total

Tommyvhod — Wed, 05 Feb 2020 07:58:00 GMT

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?