SUM column values on one table based on applying filters on another table column

[Himanshi0291]

Hi,

I want to sum a column values of one table based on applying filters on another table column. I tried the below one but it is not working. Can you please assist.

Over Time = CALCULATE(SUM(DK_Time_Temp[OvtHrs],FILTER(DK_Projects, LEFT(DK_Projects[WBS1], 1 ) <> "0" )))

[az38]

Hi @Himanshi0291 

you forgot the closing parethesys ")"

Over Time = CALCULATE(SUM(DK_Time_Temp[OvtHrs]), FILTER(DK_Projects, LEFT(DK_Projects[WBS1], 1 ) <> "0" ) )

[Himanshi0291]

Hi,

Thanks for your reply on this. The problem that I am facing with this query is that SUM function isnt working with column name "OvtHrs' that has data type 'Text'. There is no visualization and it says " SUM function cannot work with values of type String". If I change the data type to "Decimal number' in my table, What impacts do i keep in mind?

- Does it permanently change the data type in the database?

- Will it corrupt the figures already saved in this column in the table? 

Please help.

Thanks. 

[v-alq-msft · Accepted solution]

Hi, @Himanshi0291 

DAX is the language you use when you create transformations in the Power BI Desktop main screens rather than via the query editor. While M is the query language that can be viewed in the Power Query Editor. This query editor is accessed using the Edit Queries button in Power BI Desktop. It has no effect on the date type in 'Query Editor'. The aggregation calculation should work for numeric columns. You need to modify the data type as 'decimal number'. As a workaround, you may also use Value Function to Converts a text string that represents a number to a number.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.