DAX functions in EXCEL Pivot

I have a Table as data range for a Pivot Table. As I am using some text in the Data range I've created the Pivot Table, adding the data to the "Data Model"

The data column I am to be working on contains numbers - but it also can contain the text "No data".

Problem: I need to multiply two columns in the Pivot table. Using the formula: =SUMX(Table, Table[Columnn_x]*Table[Columnn_y]). It works - as long as the data doesn't contain any text: "No data"...

I need to write an IF function, checking if a cell contains a text: "No data".

I tried: =EXACT(Table[Column_x], "No data") - But I only get an error..... I have tried =EXACT("No data","No data") - That work, so the function "EXACT" is correct....

WHY!! - Please help!

This formula is invalid or incomplete: 'Calculation error in measure 'Tender_Data'[fbd417fc-0993-4cd8-84cc-6497f3eee0e6]: A single value for column 'Cost, GuardVessel WoW' in table 'Tender_Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'.


  • A simple change to your measure:

    =SUMX(Table, IFERROR(Table[Columnn_x]*Table[Columnn_y],0))

    should fix it.