Filtering results from SUMMARIZECOLUMNS

[Anonymous]

Can anyone help? I have the following code:

Data = FILTER(SUMMARIZECOLUMNS(

Invoices[Account],

Invoices[ClientID],

Invoices[CostCentre],

Invoices[Date],

Invoices[DebtorCode],

Invoices[Description],

Invoices[Gestation],

"Fees", SUM(Invoices[Fee])

), Invoices[Description] IN {"Jackets","Tops"} )

My problem is that I can filter the data coming from Invoices but I need to filter on the results of the expression where the result of the Fees expression is not equal to zero.

In other words I want to ignore returns so if a purchase is 50 then a return goes through giving another row with -50 the result of summarizecolumns will be zero.

Thanks in advance 🙂

[PattemManohar]

@Anonymous Please post the sample data and expected output, which will be helpful to suggest you an accurate solution.

[Anonymous]

Output as an example would be

Account ClientID CostCentre Date          DebtorCode Description Gestation Fees

123         6            23              20180303  26                 Trousers      6              36 

126         8            24              20180307  26                 Jacket          7              0

I could filter out the second record once I have generated the table but I don't want the second record as it was return as it is zero and the only way I know it is zero from records is because when I used SUM in SUMMARIZECOLUMNS the fee for the sale and the fee for the return cancelled each other out.

For modelling I need the Fees = 0 to be filtered out during the creation of the table not by a measure afterwarrds.

Thanks in advance.

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following code:

Data =
FILTER (
    SUMMARIZECOLUMNS (
        Invoices[Account],
        Invoices[ClientID],
        Invoices[CostCentre],
        Invoices[Date],
        Invoices[DebtorCode],
        Invoices[Description],
        Invoices[Gestation],
        "Fees", SUM ( Invoices[Fee] )
    ),
    Invoices[Description] IN { "Jackets", "Tops" }
        && [Fees] <> 0
)

Be aware that the [Fees] is the column you are using on the SUMMARIZECOLUMNS that you created, when making tables you can reference column from that virtual table to filter out values.

Regards,

MFelix

[Anonymous]

Thank you that worked perfectly. Thank you 🙂