1

The following is a simplified example of a real-world problem I'm facing: Let's say I have a Sales fact table with the Measure Count, with the dimensions Vendors, Customers, and Parts. I want to get the Min, Max, and Avg for the Sum of Sales Count for Parts, but constrained to the combinations of related Customers and Vendors.

Here's an example of the Sales table and Expected Results

Sales Table

+---------+-----------+-------------+-------+
| PartKey | VendorKey | CustomerKey | Count |
+---------+-----------+-------------+-------+
| 1       | a         | Z           | 10    |
| 1       | b         | Z           | 20    |
| 2       | a         | Y           | 30    |
| 2       | a         | Y           | 40    |
| 2       | b         | Z           | 50    |
+---------+-----------+-------------+-------+

Results

  • PartKey 1 - Min: 10 (a,Z), Max: 20 (b,Z), Avg: 15
  • PartKey 2 - Min: 50 (b,Z), Max: 70 (a,Y => 30 + 40), Avg: 60

So the measure I want to create somehow has to get the results from the data grouped by VendorKey, CustomerKey, and PartKey (though PartKey will be in the Filter Context), but I don't know how to specify that kind of grouping.

Daniel Gimenez
  • 18,530
  • 3
  • 50
  • 70

1 Answers1

1

The key here is to make a summary table where you remove the filter context and then min/max/avg using that table. The Summary table below recreated the data table you show, except the two 2,a,Y rows have been aggregated.

AvgSum =
VAR Summary =
    SUMMARIZE (
        ALL(Table1),
        Table1[PartKey],
        Table1[VendorKey],
        Table1[CustomerKey],
        "Cnt", SUM ( Table1[Count] )
    )
RETURN
    AVERAGEX (
        FILTER (
            Summary,
            Table1[PartKey] = MAX ( Table1[PartKey] )
        ),
        [Cnt]
    )

Note that if you swap out AVERAGEX above for MINX or MAXX, then you'll get your specified min and max.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Thanks for the answer. I would remove `ALL` and just have Table1: I know its not apparent in the question, but I don't want to lose any filters on the table even if it is one of the grouped columns. It works just as I expect taking `ALL` out. – Daniel Gimenez Jan 14 '19 at 20:17
  • OK. Depending on what exactly you're doing `ALLSELECTED` is another valuable option, but it it works without a modifier there, great! – Alexis Olson Jan 14 '19 at 20:39