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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Tripb44
Helper II
Helper II

Total of averages per category

 

My current data structure is such as below.  My current output and desired output shown below.  I am currently using a measure with sumx and hasonevalue for a order but I cannot get the desired split by "Cat" such as below.  Thanks for any help.

 

 

 

 

Tripb44_0-1631598271730.png

 

8 REPLIES 8
Anonymous
Not applicable

chethana_0-1631612307792.png

See if this works, not sure if you are using a calendar month or quarter in your dataset.

Do not know if the category in your file is about order booked / cancelled, ideally the same order number would populate in both cases. Ignore if this is not related to what you trying to build.

ryan_mayu
Super User
Super User

@Tripb44 

why not just use average to get the result?

avg = AVERAGE('Table'[order total])
1.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I need the grand total to be the total of the average.  Using just the average will give the average as the total.  For example, if there was an identical "Order a", lets call this "Order c", using the average only will give the grand total for "cat aa" to be 10, when I need the grand total to be 20 but still displaying the average order total per order.

@Tripb44 

is this what you want?

Measure = sumx(VALUES('Table'[Order]),[avg])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




That's what I have but I cannot seem to split the category.  In the example below, this ordernum belongs in the "1" category but it is showing up in both categories.

Tripb44_0-1631600996642.png

 

Icey
Community Support
Community Support

Hi @Tripb44 ,

 

It is suggested to check if the ordernum "108345" just belongs to the category "1". Because I can't reproduce your issue when one ordernum just belongs to one category.

 

However, when one ordernum belongs two categories. I can reproduce your issue. Please check:

avg.PNG

avg = AVERAGE('Table'[order total])
Measure 1 = sumx(VALUES('Table'[order]),[avg])
Measure 2 = 
SUMX ( 
    SUMMARIZE ( 'Table', 'Table'[order], 'Table'[cat] ), 
    [avg] 
    )

 

 

For more details, please check the attachment.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Tripb44 

since i can't reproduce the issue, could you pls provide the pbix file?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I cannot with sensitive information in my current working pbix.  The data is structured identical to how my original data layout is formated.  Any ideas is much appreciated.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.