cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mthsdiniz Frequent Visitor
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

Accepted Solutions

Re: Weighted average using summarized volume by ID

And what about this?

 

Capture.PNG

View solution in original post

6 REPLIES 6

Re: Weighted average using summarized volume by ID

Hello,

 

What about this?

 

Capture.PNG

mthsdiniz Frequent Visitor
Frequent Visitor

Re: Weighted average using summarized volume by ID

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

Re: Weighted average using summarized volume by ID

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?

Highlighted
mthsdiniz Frequent Visitor
Frequent Visitor

Re: Weighted average using summarized volume by ID

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

And what about this?

 

Capture.PNG

View solution in original post

mthsdiniz Frequent Visitor
Frequent Visitor

Re: Weighted average using summarized volume by ID

It Works!!!

Thank you man!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 170 members 1,808 guests
Please welcome our newest community members: