Forum Discussion

ITManuel's avatar
ITManuel
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Cannot understand why ALLEXCEPT not working

Hi,

 

I have the following data model:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

and the following report:

 

 

 

 

 

 

 

 

"Invoiced" is a measure calculated in the AInvoiceFlows table, "Paid" is a measure calculated in the CashIN table and "Pay delay" a measure in the "AInvoices" table. 

Now everything works fine, except the "Pay delay" on the total level per project --> 514 d is the sum of all delays from the individual invoices, which should be replaced by a weighted delay considering the weight of each indicidual invoice.

This is the calculation I would like to perform:

 

 

 

 

 

 

 

 

In order to calculate the weighting of each invoice, I would need the total invoiced amount per project for each individual invoice related to the specific project. In the case above the 1.988.221@ for each individual invoice in order to perform the weighting.

 

I tried 

TotalInvoicedperProject = 
    CALCULATE (
        [Invoiced],
        ALLEXCEPT ( ProjectFilter, ProjectFilter[FullProjectName] )
)

and several other variations  but I'm achieving only this:

 

 

 

 

 

 

 

 

I'm stucked on this can currently not figure out how to achieve the 1.988.221€ for each line in the above report.

 

Any help is much appreciated.

 

Thanks in advance

Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    ITManuel 

    The ALLEXCEPT function removes only the filters from the table specified as the first argument (i.e. 'ProjectFilter'). Using ALLSELECTED (), where no table is specifically referenced, all inner filter context is removed (including the filters from the 'AInvoices' table). 

    I would avoid using ALLEXCEPT in measures anyway as much as possible. Please read this article for more information: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ 

6 Replies

  • Barthel's avatar
    Barthel
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hey ITManuel, 

    Not sure, but maybe give this a try. Instead of using ALLEXCEPT, try using ALLSELECTED and VALUES. 

    TotalInvoicedperProject = 
    CALCULATE (
        [Invoiced],
        ALLSELECTED ( ProjectFilter ),
        VALUES ( ProjectFilter[FullProjectName] )
)

    This removes all filters for the ProjectFilter table within the visual itself (ALLEXCEPT), and keeps the project filter trough VALUES.

    • ITManuel's avatar
      ITManuel
      Icon for Responsive Resident rankResponsive Resident
      3 years ago

      Hi Barthel ,

      unfortunately this is not working either. Following the report with your proposed measure. 

       

       

       

       

       

       

      Should it be relevant, this is the [Invoiced] measure in the AInvoiceFlows table. 

      Invoiced = 
VAR _T1 =
    FILTER ( AInvoiceFlows, RELATED ( Customers[INTERCOMPANY] ) = "NO" )
VAR _Result =
    SUMX (
        _T1,
        IF (
            AInvoiceFlows[AInvoicesHeader.DOCCURRENCY] = "EUR",
            AInvoiceFlows[VALUENETDC],
            IF (
                AInvoiceFlows[AInvoicesHeader.TARGETCURRENCY] = "EUR",
                DIVIDE (
                    AInvoiceFlows[VALUENETDC],
                    AInvoiceFlows[AInvoicesHeader.XCHANGERATETC]
                ),
                VAR _DocCurrency = AInvoiceFlows[AInvoicesHeader.DOCCURRENCY]
                VAR _OrderDate =
                    RELATED ( AInvoices[INVOICEDATE] )
                VAR _FindDateWithData =
                    CALCULATE (
                        MAX ( XChangeRates[DATEXCR] ),
                        CALCULATETABLE (
                            LASTNONBLANK ( XChangeRates[DATEXCR], MAX ( XChangeRates[XCHANGERATES] ) ),
                            XChangeRates[MAINCURRENCY] = "EUR"
                                && XChangeRates[FOREIGNCURRENCY] = _DocCurrency
                                && XChangeRates[DATEXCR] <= _OrderDate
                        )
                    )
                RETURN
                    DIVIDE (
                        AInvoiceFlows[VALUENETDC],
                        LOOKUPVALUE (
                            XChangeRates[XCHANGERATES],
                            XChangeRates[DATEXCR], _FindDateWithData,
                            XChangeRates[MAINCURRENCY], "EUR",
                            XChangeRates[FOREIGNCURRENCY], AInvoiceFlows[AInvoicesHeader.DOCCURRENCY]
                        )
                    )
            )
        )
    )
RETURN
    _Result
      • Barthel's avatar
        Barthel
        Icon for Solution Sage rankSolution Sage
        3 years ago

        ITManuel 

        Does the 'INVOICECODE' in the matrix come from the 'AInvoices' table? In that case you could try the following:

         

        TotalInvoicedperProject = 
    CALCULATE (
        [Invoiced],
        ALLSELECTED (),
        VALUES ( ProjectFilter[FullProjectName] )
)