Weighted average using summarized volume by ID

[mthsdiniz]

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?

[avanderschilden]

Hello,

What about this?

[mthsdiniz]

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

[avanderschilden]

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?

[mthsdiniz]

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?

[avanderschilden · Accepted solution]

And what about this?

[mthsdiniz]

It Works!!!

Thank you man!