Forum Discussion
wrong averaged weight
I am trying to get a weighted average (as a total), that is, for each customer, :
sum(value * size) / sum(size)
Tthe value is just a measure that is (SumPerCustomer / Max_PerCustomer)
...If a customer has (3 , 1,7 ) the measure shall be 11 / 7
I get the desired result for the value but not for the averaged weight:
VAR Max_PerCustomer =
MAXX(
FILTER(
SUMMARIZE(
Revenue
,'Customer'[Customer]
,'Center'[Center]
)
, SUM(Revenue[Revenue]) > 0
)
, SUM(Revenue[Revenue])
)
VAR SumPerCustomer =
CALCULATE(
SUM(Revenue[Revenue])
, FILTER(
SUMMARIZE(
Revenue
,'Customer'[Customer]
,'Center'[Center]
)
, SUM(Revenue[Revenue]) > 0
)
)
RETURN
SWITCH(
TRUE()
,ISINSCOPE('Customer’[Customer]) , DIVIDE(SumPerCustomer, Max_PerCustomer)
,DIVIDE ( SUMX('Customer’, DIVIDE(SumPerCustomer, Max_PerCustomer) * SumPerCustomer), SUMX('Customer’, SumPerCustomer),0)
)
Any help is appreciated, I can't figure why it does the wrong calculation
4 Replies
Anonymous , try to add values and check
measure =
var _sel = selectedvalue(Param[Param])
var _cnt =distinctcount(Geography[locations])
var _1= CALCULATE([Sales],TOPN(_sel,all(Geography[locations]),[Sales],DESC),VALUES(Geography[locations]))
var _2= CALCULATE([Sales],TOPN(_cnt,all(Geography[locations]),[Sales],asc),VALUES(Geography[locations]))
return
if(_sel =0 || not(isfiltered(Param[Param])) , _2, _1)
calculate(
SWITCH(TRUE()
,ISINSCOPE('Customer'[Customer]) , DIVIDE(SumPerCustomer, Max_PerCustomer)
,DIVIDE ( SUMX('Customer', DIVIDE(SumPerCustomer, Max_PerCustomer) * SumPerCustomer), SUMX('Customer', SumPerCustomer),0)
), values('Customer'[Customer]))
Anonymous , values is group data at a level
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Anonymous
could you pls provide the sample data and expected output?
