Dynamicaly Filtered Calculated Table

[RobertSlattery]

I have a table of summarised invoices with [sales value], [line count], [branch] and [date].

I want to create a histogram to show the distribution of sales value per line count and I want it to respond to page filters.

This also needs to be summarised at different granularity like, daily and weekly, average sales per line.

So I need grouped tables that respond to filters. 

My first strategy for the histogram bins is to use the [right click] New Group functionality to automatically allocate the rows (by day and by week respectively) of the summarised tables to the sales value per line bins.

I created calculated tables to group summarise the invoice table...

(this is done by selecting New Table from the Modelling tab and pasting the code below in the formula bar)

Weekly Sales per Line = CALCULATETABLE(
    GROUPBY(
        'Invoice_Summary All',
        'Dim Date'[Year],
        'Dim Date'[Month],
        'Dim Date'[Week Number],
        "Week Starting",
        MINX(CURRENTGROUP(), VAR d = RELATED('Dim Date'[Date]) return d - WEEKDAY(d, 2) + 1),
        "Ave Weekly Sales per Line",
        AVERAGEX(CURRENTGROUP(), 'Invoice_Summary All'[Sales Per Line]),
        "Lines",
        SUMX(CURRENTGROUP(), 'Invoice_Summary All'[Order Lines]),
        "Count",
        COUNTX(CURRENTGROUP(), 'Invoice_Summary All'[IHINVN])
    ),
    FILTER(
        ALLSELECTED('Invoice_Summary All'),
        [Order Lines] <> 0 && [Type] <> 2
    )
)

I then created Groups on the [Ave Weekly Sales per Line] column which automatically creates an [Ave Weekly Sales per Line (bins)] column for me.

Then I plot  [Count per Agent] against [Ave Weekly Sales per Line (bins)] and it works perfectly. 

I have a relationship from my Date table to the [Week Starting] field in the calculated table, so it responds to the Date slicer also.

The problem is, if I select a particular branch, using a treemap, the resultant filtering is not reflected in the calculated table and therefore the histogram.  This is because the calculated table

How can I make the calculated table respond to the filters on the parameter table?

I can manually filter the calculated table by adding another filter for branch, perhaps there is a way to do this pragmatically?

I tried solving it with two tables and an IF function but it throws, seems IF is scalar only...

Weekly Sales per Line = CALCULATETABLE(
    if(
        HASONEVALUE(States[Branches.State]),
        'Weekly Sales Per Line Grouped',
        'Weekly Sales per State per Line'
    )
)

My next thought was to use the GROUPBY function in a measure on the Bins table like this...

BinLinesWeekly = 
    VAR _grouped = CALCULATETABLE(
        GROUPBY(
            'Invoice_Summary All',
            'Dim Date'[Year],
            'Dim Date'[Week Number],
            "Week Starting",
            MINX(CURRENTGROUP(), VAR d = RELATED('Dim Date'[Date]) return d - WEEKDAY(d, 2) + 1),
            "Lines",
            SUMX(CURRENTGROUP(), 'Invoice_Summary All'[Order Lines]),
            "Ave Weekly Sales per Line",
            AVERAGEX(CURRENTGROUP(), 'Invoice_Summary All'[Sales Per Line])
        ),
        FILTER(
            ALLSELECTED('Invoice_Summary All'),
            [Order Lines] <> 0 && [Type] <> 2
        )
    )
    VAR l = MIN(Bins1000[Bin2Lower])
    VAR u = MAX(Bins1000[Bin2Upper])
    RETURN
        CALCULATE(
            SUMX(_grouped, [Lines]),
            FILTER(
                _grouped,
                AND(
                    [Ave Weekly Sales per Line] > l,
                    [Ave Weekly Sales per Line] <= u
                )
            )
        )

This calculates the sumarised table on the fly, recalculating it every time the filters change.  This has some strange behaviour though, the filter on _grouped doesn't work and the SUMX is calculated over the whole table, returning the total of [Lines] in every bin.

I have created a minimum working example here I tried a few different ways to cross-check my attempt, including static calculated tables.  One uses a measure to apply the bin filter based on the row context of the Bins table and the other uses Groups to tag the rows with the bin value.  The odd thisng is, if I relate the Grouped version to the Bins table, the solution above seems to work and if I delete or disable the relationship, it breaks again.  I marked the relationship I'm talking about in the pic below...

Can anybody help me out? I feel like it's very close.

[RobertSlattery · Accepted solution]

As the blues bard says: I woke up this morning...

Here is a solution that is fast and stable.

This works and DAX is blindingly fast...

BinLinesWeekly = 
    VAR l = MIN(Bins1000[Bin2Lower])
    VAR u = MAX(Bins1000[Bin2Upper])
    VAR _grouped = Filter(
        CALCULATETABLE(
            GROUPBY(
                'Invoice_Summary All',
                'Dim Date'[Year],
                'Dim Date'[Week Number],
                "Week Starting",
                MINX(CURRENTGROUP(), VAR d = RELATED('Dim Date'[Date]) return d - WEEKDAY(d, 2) + 1),
                "Lines",
                SUMX(CURRENTGROUP(), 'Invoice_Summary All'[Order Lines]),
                "Ave Weekly Sales per Line",
                AVERAGEX(CURRENTGROUP(), 'Invoice_Summary All'[Sales Per Line])
            ),
            FILTER(
                ALLSELECTED('Invoice_Summary All'),
                [Order Lines] <> 0 && [Type] <> 2
            )
        ),
        AND(
            [Ave Weekly Sales per Line] > l,
            [Ave Weekly Sales per Line] <= u
        )
    )
    RETURN
        SUMX(_grouped, [Lines])

 It's also a very beautiful illustration of the Central Limit Theorem. 

[RobertSlattery]

Here is one solution but it's prone to crashing...

BinLinesWeekly = 
    VAR _grouped = CALCULATETABLE(
        GROUPBY(
            'Invoice_Summary All',
            'Dim Date'[Year],
            'Dim Date'[Week Number],
            "Week Starting",
            MINX(CURRENTGROUP(), VAR d = RELATED('Dim Date'[Date]) return d - WEEKDAY(d, 2) + 1),
            "Lines",
            SUMX(CURRENTGROUP(), 'Invoice_Summary All'[Order Lines]),
            "Ave Weekly Sales per Line",
            AVERAGEX(CURRENTGROUP(), 'Invoice_Summary All'[Sales Per Line])
        ),
        FILTER(
            ALLSELECTED('Invoice_Summary All'),
            [Order Lines] <> 0 && [Type] <> 2
        )
    )
    VAR l = MIN(Bins1000[Bin2Lower])
    VAR u = MAX(Bins1000[Bin2Upper])
    RETURN
        CALCULATE(
            SUMX(_grouped, 
                IF(
                    AND(
                        [Ave Weekly Sales per Line] > l,
                        [Ave Weekly Sales per Line] <= u
                    ),
                    [Lines]
                )
            )