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 item and some items have 1 or more Batch. The idea is to get for every Item an average (calculated based on the weight of the column Volume). 

As you can see there is a summary where is calculated the averages, min and max for every Item (in Yellow) and the total average for all the data.

I need to get the "blue" calculation. Is that possible in Power BI.

 

Regards

Need Help

3 Replies

  • 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

  • P3Tom's avatar
    P3Tom
    Helper I
    9 years ago

    Yes, that is possible. You will need to know something about iterators like SUM and AVERAGEX, plus you will need to know how to check if you are on a detail line or a total (typically done with check phrases wiith an IF and a HASONEVALUE functions). 

     

    I recommend Rob Collie's and Avi Singh's book, "Power Pivot and Power BI" asa well as a free DAX reference card which you can download from here.

     

    Best regards,

     

    Tom

    www.powerpivotpro.com

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi rulloa,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

     

    Best Regards!
    Dale