cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShipraJain
Helper I
Helper I

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.

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.

 

ShipraJain_0-1649655429748.png

 

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

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

 

View solution in original post

9 REPLIES 9
ShipraJain
Helper I
Helper I

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.

 

ShipraJain_0-1649660338379.png

Thanks

Shipra

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
Super User
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

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.

 

ShipraJain_0-1651821304884.png

 

 

Thanks,

Shipra Jain

 

Please find the clean screenshot.

image.png

Thanks

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

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

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.....

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors