Count if measure returns true - error with total value

[Anonymous]

Hi, 

I try to count how many documents have a final cost amount >= 500euro.
For 10 nov 2020 this should return 7.

I created a measure that returns a correct value per row (see table in screenshot).

But the total value is wrong. It says 279 (which is the count of all rows) instead of 7.

I tried different measures but nothing is working....
In the above screenshoted table the VAR Test1 is applied. 

Anyone knows how I need to modify the measure? 

Julie

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here's my solution.

You can modify the measure like this:

Final Cost Amount>500euro = 
VAR _T =
    ADDCOLUMNS (
        'FactControlReportsALL',
        "Requirment", IF ( [Final_Cost_Amount] >= 500, 1, BLANK () )
    )
RETURN
    SUMX ( _T, [Requirment] )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi @v-yanjiang-msft , it is still not working. 
The row values are now correct, but the total value is wrong. 

[v-yanjiang-msft]

Hi @Anonymous ,

Isn't "Final Cost Amount" a measure? It seems a measure in your original post.

Best Regards,
Community Support Team _ kalyj

[Anonymous]

Yes [Final Cost Amount] was a measure. 

[Final Cost Amount] = SUM(FactControlReportsALL[Final_Cost_Amount])

I wrote it out in the 'final cost amount >= 500' measure instead of refering to the 'Final Cost Amount' measure (which simply is a sum of final cost amount). This to avoid confusion 

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

In the formula, try to use the measure [Final Cost Amount] directly, but not SUM(FactControlReportsALL[Final_Cost_Amount])

Best Regards,
Community Support Team _ kalyj

[Anonymous]

It worked ! Thanks ! 🤗

But I don't understand what is the difference between refering to another measure (which works) and includind the calculation in the measure itself (not working)?

As in both cases it is the same calculation? Could you explain?

[v-yanjiang-msft]

Hi @Anonymous ,

It's my pleasure! In this formula maybe because the ADDCOLUMN function, it means create a new table in the measure, you can refer to this artical.

Reference:ADDCOLUMNS function (DAX) - DAX | Microsoft Docs

Best Regards,
Community Support Team _ kalyj

[Anonymous]

Hi @v-yanjiang-msft , This solution does not work unfortunantely. 
For precision: in my data one document number can have several rows with different articles. 
In the below table the column document_number is a distinct count.

[v-yanjiang-msft]

Hi @Anonymous ,

Try to modify the measure like this:

Final Cost Amount>500euro = 
VAR _T =
    ADDCOLUMNS (
        DISTINCT('FactControlReportsALL'[Document_Number]),
        "Requirment", IF ( [Final_Cost_Amount] >= 500, 1, BLANK () )
    )
RETURN
    SUMX ( _T,[Requirment] )

Best Regards,
Community Support Team _ kalyj

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

[ValtteriN]

Hi,

I think the problem is with your [Final cost amount] measure. I suppose it return more than 500 to all rows.

Now if you use this as your condition in test1 it will return true to all rows since the sum is greater. It seems you use a column instead of your measure in your table. To create the condition measure you can use something like:

Final cost Amount test = SELECTEDVALUE('Table'[Final_cost_amount])

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[Anonymous]

Hi @ValtteriN , indeed the VAR Requirement refers to a measure. Sorry forgot to precise this.

[Final Cost Amount] = SUM(FactControlReportsALL[Final_Cost_Amount])

I tried to use SELECTEDVALUE. 

Created VAR Requirement2 and used this in VAR Test1. But it does not behave as expected.

One document number can have several rows in the data (different articles with the same document number). It seems that this measure returns 1 when the final cost amount is >= 500 for one article/row. It should return 1 if the final cost amount is >= 500 for the document number (so sum of final cost amount of the different rows with same document number). 
Also the total value is blank 😕

[smpa01]

@Anonymous  is it kindly possible to provide a sample pbix?