Calculate average of a measure over multiple tables, with a field parameter

[Connor888]

This is a little like a recent question I asked here. I am using a field parameter to produce (among other things) a table visual with columns from two different tables, and measures. Using the similar dummy data to last time here are some tables to illustrate:

Customer:

Customer Region	Customer Segment	Customer Name
Europe	Industrial	Foo
Europe	Industrial	Bar
Americas	Tech	Baz

Product:

Product Number	Product Category
A	Hats
B	Shoes
C	Shoes

Sales:

Product Number	Customer Name	Units Sold
A	Foo	10
A	Bar	5
B	Baz	5
B	Bar	12
C	Foo	7
C	Baz	2

So I have a measure which calculates the total sales (in fact its a bit more complicated but this shouldn't affect things, I hope), and in my table visual any of the non-numeric columns might appear based on the field parameter. What I want to do is calculate the average of this total measure at a regional level with respect to the fields currently selected. This won't actually be showing in the table, its an intermediate in other calcualtions, but I'll show it in my example below to explain the value I need.

So for example with [Customer Region] and [Product Category] selected we would see:

Customer Region	Product Category	Total Sales	Average Sales
Europe	Hats	15	13.7
Europe	Shoes	19	13.7
Americas	Shoes	7	13.7

I have tried using AVERAGEX here but since the columns selected can change (and come from two different tables!) I'm not sure what to include as the first parameter. My instinct suggests I need a DAX formula that returns a table identical to the table visual to iterate over, but I can't figure out how to do this. Any ideas?

[v-kongfanf-msft]

Hi @Connor888 ,

Maybe you can try below formula to create calculated table:

Table =
VAR total_ =
    CALCULATE ( SUM ( Sales[Units Sold] ) )
VAR table_ =
    SUMMARIZE ( Sales, [Customer Region], [Product Category] )
VAR result =
    SUMMARIZE (
        Sales,
        [Customer Region],
        [Product Category],
        "category_total", CALCULATE ( SUM ( Sales[Units Sold] ) ),
        "avg",
            DIVIDE (
                CALCULATE ( SUM ( Sales[Units Sold] ), ALL ( Sales ) ),
                COUNTROWS ( table_ )
            )
    )
RETURN
    result

Best Regards,
Adamk Kong

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

[Connor888]

Hi, this would work if I was just using a static selection of columns, but I have a field parameter involved so it may not be [Customer Region] and [Product Category], it could be any combination of dimensions.