Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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.
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.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |