cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mthsdiniz
Frequent Visitor

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?

1 ACCEPTED SOLUTION

And what about this?

 

Capture.PNG

View solution in original post

6 REPLIES 6
avanderschilden
Resolver I
Resolver I

Hello,

 

What about this?

 

Capture.PNG

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


Weighted Average.PNG

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?

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?

And what about this?

 

Capture.PNG

View solution in original post

It Works!!!

Thank you man!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors