How to have dynamic aggregation in PowerPivot?

[Anonymous]

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:

KPI	Date	Value
Sales	01/01/2021	142
Sales	02/01/2021	163
Sales	03/01/2021	184
Sales	04/01/2021	205
Sales	05/01/2021	226
Stock	01/01/2021	164
Stock	02/01/2021	172
Stock	03/01/2021	180
Stock	04/01/2021	188
Stock	05/01/2021	196

After I pivot it, it becomes:

Sum of Value	Column Labels
Row Labels	01/01/2021	02/01/2021	03/01/2021	04/01/2021	05/01/2021
Sales	142	163	184	205	226
Stock	164	172	180	188	196
Grand Total	306	335	364	393	422

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

Thanks!

[Jihwan_Kim]

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 ()
)

[amitchandak]

@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]))