Averagge of group when select another column

[Anonymous]

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.

Please find the steps below:

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

[Anonymous]

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

[Vijay_A_Verma · Accepted solution]

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

[Vijay_A_Verma]

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]

Hi @Vijay_A_Verma ,

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]

Please find the clean screenshot.

Thanks

[Vijay_A_Verma]

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

[Anonymous]

Hi @Vijay_A_Verma,

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

[Vijay_A_Verma]

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]

Hi @Vijay_A_Verma,

I have shared the file on Google Drive. Please find the link below. Also, please share your Email Address so I can share the file with you.

https://docs.google.com/spreadsheets/d/1WlZkp2XM_J2HO9-n2TLJwIOc_dsuzvaf/edit?usp=sharing&ouid=11402...

Thanks

Shipra