topic Re: Weighted average using summarized volume by ID in DAX Commands and Tips

Weighted average using summarized volume by ID

mthsdiniz — Tue, 02 Jul 2019 17:19:25 GMT

Hey guys, i have a question about how to do calculate a different weighted average...

Basically i have more than one line by slip, and i need to use the summarized value by slip to use in this average. Follows bellow the example:

WRONG WAY TO DO IT

NR_SLIP PREMIUM_BASE PREMIUM MEASURE (PREMIUM_BASE / PREMIUM)

2674739 0 -177 0

2674739 171,85 177 0,97

2674555 1040,76 708 1,47

2674558 199 0 0

TOTAL 1411,61 708 1,99

intead of dividing the PREMIUM_BASE by the PREMIUM, i need to divide the PREMIUM_BASE by the summarized value of the PREMIUM by NR_SLIP... The result would be like that:

NR_SLIP PREMIUM_BASE PREMIUM SUMMARIZED_PREMIUM MEASURE (PREMIUM_BASE / PREMIUM)

2674739 0 -177 0 0

2674739 171,85 177 0 0

2674555 1040,76 708 708 1,47

2674558 199 0 0 0

TOTAL ----- ----- ----- 1,47

I want to have the result on the column measure

This is what i'm doing now....

Summarized Volume --------------------------------------------

Cummulative_Premio_Net = CALCULATE ( [PREMIO_TON]; FILTER ( ALLSELECTED ( RealTimeData ); RealTimeData[NR_SLIP] = MAX ( RealTimeData[NR_SLIP] ) ); FILTER ( ALLSELECTED ( RealTimeData ); RealTimeData[NR_SLIP] <= MAX ( RealTimeData[NR_SLIP] ) ) )

Pemium_Base -----------------------------------------

Base Premio = IF([Cummulative_Premio_Net]<>0;SUM(RealTimeData[BASE_PREMIO]);0)

Measure ---------------------------------------------------------

PREMIO_PORTO_BOD = DIVIDE ( SUMX ( RealTimeData; DIVIDE ( ROUND ( [Base Premio]; 8 ); [PREMIO_TON]; 0 ) * [PREMIO_TON] ); [PREMIO_TON]; 0 )

The problem with the measures above is that they are very costly and constantly making my visuals to not load...
I also cannot create any tables, calculated columns neither enter the power query to make changes ...

Any ideas?

Re: Weighted average using summarized volume by ID

avanderschilden — Tue, 02 Jul 2019 20:53:39 GMT

Hello,

What about this?

Re: Weighted average using summarized volume by ID

mthsdiniz — Tue, 02 Jul 2019 21:14:25 GMT

Hey avanderschilden,

looking at a line level it worked, but when i looked at the total it did not worked :/

The measure is correct one and the measure 2 is your measure. It is weighted by line, but the total is not working =/

1533,96 / 1068 = 1,4362

Re: Weighted average using summarized volume by ID

avanderschilden — Tue, 02 Jul 2019 21:20:24 GMT

Excuse me but I don't understand what you mean.

What is the 1533,96? The total displays a different number; 1824,96.

And you say measure 2 is wrong, but it seems measure 1 is producing the correct result also on the total line.

So what is actually the problem now?

Re: Weighted average using summarized volume by ID

mthsdiniz — Wed, 03 Jul 2019 17:25:13 GMT

Hey avanderschilden ,

Sorry for not explaining in a right way...

1533,96 is the sum of premium_base in the lines that the premium is not 0...

i need to consider values for the premium base only if the the sum of it by id is different from 0...

The measure 1 works but it is not perfomatic at all.... Your measure (measure 2) works when we look at the lines, but when we look at the total it gives a result considering the whole premium base value...

Do you know another way to make it happens?

Re: Weighted average using summarized volume by ID

avanderschilden — Thu, 04 Jul 2019 07:14:10 GMT

And what about this?

Re: Weighted average using summarized volume by ID

mthsdiniz — Thu, 04 Jul 2019 11:47:35 GMT

It Works!!!

Thank you man!