Calculate the average ignoring a specific filter

[Dave93]

Hello, I'm having trouble calculating the total average of payment days. The situation is as following:

I have 4 tables that are connected to my calculation:

1. 'Invoice History': stores all the invoices with an InvoiceNr, debtorNr and an Invoice_Date
2. 'Payment Behaviour': stores an InvoiceNr and the PaymentDays (= a number that indicates te days between the invoice_date and the payment date)
3. 'Debtors': Stores the information of all the debtors
4. 'Dates': A table with all dates which are relevante to my report.

I have already made a measure that calculates the average payment days of all the individual debtors:

Average PaymentDays = 
    CALCULATE (
        AVERAGE ( 'Payment Behaviour'[PaymentDays] );
        FILTER (
            'Invoice History';
            RELATED ( 'Payment Behaviour'[InvoiceNr] ) = 'Invoice History'[InvoiceNr]
        )
    )

I used a filter because not all the invoices in the 'invoice history' table are paid yet. So I looked for my calculation only on the paid invoices. Now I want to make a measure that calculates the average payment days of all the debtors combined ignoring any debtor that I've selected in a slicer. I tried a lot of different things already but I can't figure out what I'm doing wrong. Every time I make a measure it gives back the average payment days of alle the debtors ignoring even the months that are selected. So I get back the same average for every period. I just want to ignore the selected debtors. Not the selected dates. 

This is the last thing I tried:

Average PaymentDays Overall = 
    CALCULATE (
        AVERAGE ( 'Payment Behaviour'[PaymentDays] );
        FILTER (
            ALLEXCEPT('Invoice History'; Dates[date]);
            RELATED ( 'Payment Behaviour'[InvoiceNr] ) = 'Invoice History'[InvoiceNr]
        )
    )

Thanks in advance!

[v-qiuyu-msft]

Hi @Dave93,

In your scenario, which visual did you place this measure in? Did you only want to ignore slicer selection for this measure, or the whole visual contains this measure? If it's latter, you can Edit Interaction to force the visual can't be filtered by the slicer. Otherwise, please share some sample data about those four tables for our analysis.

Best Regards,
Qiuyun Yu

[Dave93 · Accepted solution]

Hi,

I only want to ignore slicer selection for the measure beceause I want to compare the overall average payment behaviour with the average payment behaviour of the selected debtor. So I want to place multiple measures in a (line-chart) visual. So editing the interaction of the visual won't work in my case.

Here some additional information:

I've already made a workaround by creating a additional table but I hope there is a sollution without having to do so. The workaround I made is the following:

Payments = 
FILTER (
    SUMMARIZE (
        'Invoice History';
        'Invoice History'[InvoiceNr];
        "PAID"; AVERAGE ( 'Payment Behaviour'[PaymentDays] );
        "DATUM"; DISTINCT ( 'Invoice History'[DateInvoice] )
    );
    NOT ( ISBLANK ( [PAID] ) )
)

This new table has some releationships with the 'Invoice History' and the 'Datum' table, but without the Debtors table. But I hope I can get the same results without creating this second table.

[mmainza]

hey there. Were you ever able to find the solution you were looking for?

I have this same problem and would very much prefer not to make a new table. I figured an ALL or ALLEXCEPT should work, but the measure refuses to ignore a specific filter.