Forum Discussion

mthsdiniz's avatar
mthsdiniz
Frequent Visitor
7 years ago
Solved

Weighted average using summarized volume by ID

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?

6 Replies

    • mthsdiniz's avatar
      mthsdiniz
      Frequent Visitor

      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's avatar
        avanderschilden
        Resolver I

        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?