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

Highlighted
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?

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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors