Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

 

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

Anonymous
Not applicable

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

 

Anonymous
Not applicable

Please find the clean screenshot.

image.png

Thanks

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors