Forum Discussion

yolandecastle's avatar
yolandecastle
Regular Visitor
4 years ago
Solved

Calculated Measure not Summing correctly

hi there

newby to PowerBi here.

I need to calculate a weighted average on the age of product consignments sold over several days. I get it right on excel with the sumproduct formula. But o my, it's not working on Bi. The values in yellow is what i get on excel and what my answer should be.

 

however,

in PowerBi i get the following:

my measures are as follows:

a1DAY_TOTAL = CALCULATE(SUM(SALES[SALES_QTY]),ALL(SALES[SALES_DATE]),FILTERS(SALES[DEL_GRN]))

 

a2QTY_PERC = DIVIDE(SUM(SALES[SALES_QTY]),[a1DAY_TOTAL])

 
a3AVE_AGE = AVERAGE(SALES[a0AGE])
 
a4AVE_PERC = ([a3AVE_AGE]*[a2QTY_PERC])
 
.....I need a4AVE_PERC to have a total of 4.9. 
 
I would appreciate your help!
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, yolandecastle ;

    You could create measure as follows:

     

    averge = 
var _a=SUMMARIZE('Table',[DEL_ NOTE ],[SALES_ _QTY],"1", SUM([SALES_ _QTY])*SUM([AGE IN DAYS]))
return DIVIDE( SUMX(_a,[1]),SUM([SALES_ _QTY]))

     

    The final output is shown below:

     


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, yolandecastle ;

    You could create measure as follows:

     

    averge = 
var _a=SUMMARIZE('Table',[DEL_ NOTE ],[SALES_ _QTY],"1", SUM([SALES_ _QTY])*SUM([AGE IN DAYS]))
return DIVIDE( SUMX(_a,[1]),SUM([SALES_ _QTY]))

     

    The final output is shown below:

     


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.