Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to have dynamic aggregation in PowerPivot?

Hello

 

I create a pivot in Excel and I put a field in the 'Values' area of the pivot. There I have to choose the type of aggregation, like sum, average etc.

 

Is it possible to choose different type of aggregation when a specific field contains a *string* and another type of aggregation for the other fields?

 

For example, my raw data is:

 

KPIDateValue
Sales01/01/2021142
Sales02/01/2021163
Sales03/01/2021184
Sales04/01/2021205
Sales05/01/2021226
Stock01/01/2021164
Stock02/01/2021172
Stock03/01/2021180
Stock04/01/2021188
Stock05/01/2021196

 

After I pivot it, it becomes:

 

Sum of ValueColumn Labels   
Row Labels01/01/202102/01/202103/01/202104/01/202105/01/2021
Sales142163184205226
Stock164172180188196
Grand Total306335364393422

 

How can I make it to sum the Sales and average the Stock?

 

Thanks!

powerpivot

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , If the model has been taken from power BI

    Best is two measure

    Sales sum= calculate(sum(Table[Value]), filter(Table, Table[KPI] = "Sales"))

     

    Stock Avg= calculate(Average(Table[Value]), filter(Table, Table[KPI] = "Stock "))

     

     

    or

    If(Max(Table[KPI])= "Sales", Sum(Table[Value]) , Average(Table[Value]))

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    Expected result: =
SWITCH (
    SELECTEDVALUE ( Data[KPI] ),
    "Sales", SUM ( Data[Value] ),
    "Stock", AVERAGE ( Data[Value] ),
    BLANK ()
)