Total of averages per category

[Tripb44]

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.

[Anonymous]

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]

@Tripb44 

why not just use average to get the result?

avg = AVERAGE('Table'[order total])

[Tripb44]

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.

[ryan_mayu]

@Tripb44 

is this what you want?

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

[Tripb44]

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.

[Icey]

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

[ryan_mayu]

@Tripb44 

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

[Tripb44]

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.