cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Averagge of group when select another column

Hi Team,

Apologies if I have posted in the wrong way. I need to calculate Average of category if I selected any brand of that category.

1. I have brands and category
2. If I select one brand like "Apple" then Sum of total apple brand and Average of category where Apple brand available and Maximum of category where Apple brand available.

Now, if I selected "Apple" then I got sum but how I can achieve Average of Telecom category and Max of Telecom category.

Thank you

Shipra

1 ACCEPTED SOLUTION
Super User

You will need to create 2 measures for Max and Average and drag that to your pivot.

Solution file depicting this @ https://1drv.ms/x/s!Akd5y6ruJhvhuU0VHOR-v6MCtdKU?e=dsvCqQ

``Max:=CALCULATE(MAX(Table1[Traffic Out]),ALLEXCEPT(Table1,Table1[Dept]))``
``Avg:=CALCULATE(AVERAGE(Table1[Traffic Out]),ALLEXCEPT(Table1,Table1[Dept]))``

9 REPLIES 9
Anonymous
Not applicable

Hello Vijay,

Thanks for the help.

But I guess I am not able to explain my problem properly. Please find the attached sheet with output.

Thanks

Shipra

Super User

You will need to create 2 measures for Max and Average and drag that to your pivot.

Solution file depicting this @ https://1drv.ms/x/s!Akd5y6ruJhvhuU0VHOR-v6MCtdKU?e=dsvCqQ

``Max:=CALCULATE(MAX(Table1[Traffic Out]),ALLEXCEPT(Table1,Table1[Dept]))``
``Avg:=CALCULATE(AVERAGE(Table1[Traffic Out]),ALLEXCEPT(Table1,Table1[Dept]))``

Super User

I presume you want to look at a pivot based solution.

Just drag the Sales - SKU (AED) field again in values section.

Click on dragged field - Value field settings - Average - This will give you average.

Drag the Sales - SKU (AED) again in values section again

Click on dragged field - Value field settings - This time select Max to get Max

Anonymous
Not applicable

Thanks for the previous suggestion, it was working for me but now I add one more column name as "Date". Now I calculate Maximum value, it is showing wrong result but it is working fine for Average calculation. Please find the below Screenshot. In the SS, Apple Location in Telecom Dept have maximum Traffic_out which is "3361" but in the Max Traffic Out column it is showing "3103".

Thanks for the help.

Thanks,

Shipra Jain

Anonymous
Not applicable

Thanks

Super User

After 'InStoreDetails_Sum'[Department], put a comma and then 'InStoreDetails_Sum'[Date]

Anonymous
Not applicable

Thanks for the replay.

I have used this formula but it is not working.

Max Traffic Out = CALCULATE(MAX('InStoreDetails_Sum'[Sum_TrafficOut]),ALLEXCEPT('InStoreDetails_Sum','InStoreDetails_Sum'[Department]),ALLEXCEPT(InStoreDetails_Sum,InStoreDetails_Sum[Date]))

Thanks
Shipra Jain
Super User

Can you give me some sample data (without any confidential data) to work on? If possible, you can upload to Onedrive and share the link here.....

Anonymous
Not applicable

Thanks

Shipra