SUMMARIZECOLUMNS: Excluding rows with blank values in one of the colums

[marcin_wozniak]

Hi. I am creating a new table using summarize columns, with the following expression

Table = SUMMARIZECOLUMNS(sap_account_data[company];sap_account_data[account_number];sap_account_data[year];sap_account_data[month];sap_account_data[status];"Category_L1";"";"Category_L2";"Turnover";"Value";CALCULATE(sap_account_data[Amount];SAP_Dict[L8]="Turnover - External"))

I want to limit the output of the formula and exclude rows generated by the formula that have are BLANK in column "Value"

Any tips on how to to that?

[v-piga-msft]

Hi @marcin_wozniak,

Have you solved the problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output so that we could help further on it?

Best Regards,

Cherry

[TomMartens]

Hey,

you can use something like this

CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            'Table1'
            ,Table1[category]
        )
    ,"myamount",CALCULATE(SUM('Table1'[amount]))
    )
    ,'Table1'[value] <> BLANK()
)

Just wrap CALCULATETABLE() around your summarize and use

,'Table1'[value] <> BLANK()

to filter out the rows that are blank in the column.

Hopefully this is what you are looking for

I'm wondering if this a a direct query connection to your source data, if not you may also consider to filter out the rows that have blank values inside the column using Power Query. I would consider this.

Regards,

Tom