DAX Average at different grain

Question

Ok, highly simplified table of three columns, order#, product#, and quantity...

Order | Product | Qty
1     | A       | 10
1     | B       | 20
2     | C       | 30

I want to calculate an average of quantity, so.. this is at the "default grain":

AvgQty = 60/3 = 20

Easy, however, i also then want to remove Product:

Order | Qty
1     | 30
2     | 30

and now the Qty should re-aggregate [as they would with a sum()], and now I would want AvgQty to return the average of these new lines...

AvgQty = 60/2 = 30

If tried to do this by grouping by Order explicitly like so:

measure :=
IF (
    ISFILTERED ( 'Table'[Product] ),
    AVERAGEX (
        SUMMARIZE (
            'Table',
            'Table'[Order],
            'Table'[Product],
            "SumQty", SUM ( 'Table'[Qty] )
        ),
        [SumQty]
    ),
    AVERAGEX (
        SUMMARIZE (
             'Table',
             'Table'[Order],
             "SumQty", SUM ( 'Table'[Qty] ) ),
        [SumQty]
    )
)

It doesn't quite work due to the total of the column technically not being filtered by product, so it continues to still show the incorrect total...

I am not certain how to override this..?

My actual calc is not just a simple average, but the main problem I am facing is ensuring I can get a 'recalculation' of the Qty at a new grain.. if I can nail this, I can fix my own problem.. the solution could well be to also load the table to the model at the order grain too!!! ;)

Answer 1

I also thought about it the last days and I am afraid there is no way to solve this for the following reasons:

• there is no function in DAX to return the whole table that was calculated as your rows
• there is no function to tell you what was aggregated there
• for a single row you could find out what was filtered using complex cascading ISFILTERED functions but this is not really feasible nor reliable
• the biggest problem: when you are on the total or sub-total level, there is no way to find out what was used for the detail rows as none of the existing functions like ISFILTERED, HASONEVALUE, etc. would work

so for DAX this cannot be solved at the moment from my point of view

in case you are using MDX to query your model (e.g. a Pivot Table) you could create a MDX measure which uses the AXIS()-function to return the set which was used on rows/columns and is it in a COUNT() function