Forum Discussion

rulloa's avatar
rulloa
Regular Visitor
9 years ago

Sumx / grouping by

https://drive.google.com/open?id=0B0ePBdXMgWl5dGZ0bFVhbDZxYUk Hello: I need to create a new calculation which is like sum.product/sum in excel. In the example,  the excel file has a column call it...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi rulloa,

 

It would be a good idea to follow the steps you did in the excel workbook. 

1. Create a summary of the data.

SummarizeTable =
SUMMARIZE (
    'Table1',
    Table1[Item],
    "SummarizedVolume", SUM ( Table1[Volume] ),
    "AverageOfSumProduct", SUMX ( 'Table1', 'Table1'[Volume] * 'Table1'[Average] )
        / SUM ( Table1[Volume] ),
    "SummarizedMin", MIN ( Table1[Min] ),
    "SummarizedMax", MAX ( Table1[Max] )
)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. Create four measures to get the results.

 

FinalTotalVolume =
SUM ( 'SummarizeTable'[SummarizedVolume] )
FinalSumProduct =
SUMX (
 SummarizeTable,
 SummarizeTable[SummarizedVolume] * SummarizeTable[AverageOfSumProduct]
)
 / SUM ( 'SummarizeTable'[SummarizedVolume] )


FinalMinProduct =
SUMX (
    SummarizeTable,
    'SummarizeTable'[SummarizedMin] * 'SummarizeTable'[SummarizedVolume]
)
    / SUM ( 'SummarizeTable'[SummarizedVolume] )
FinalMaxProduct =
SUMX (
    'SummarizeTable',
    'SummarizeTable'[SummarizedMax] * 'SummarizeTable'[SummarizedVolume]
)
    / SUM ( 'SummarizeTable'[SummarizedVolume] )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Note: there are errors of 6007802 in the workbook, this is the right results.

 

Best Regards!

Dale