Forum Discussion

Thomas_S's avatar
Thomas_S
Frequent Visitor
3 years ago
Solved

Count calculation result

Hello

 

I'm trying to count the result of a calculated measure but i'm stucked...

 

I have 2 distinct tables (with many other differents columns, extracted from 2 differents "tools"), looking like that (simplified version) :

 

Table 1 : 

id      Amount

1       5

2       10

3       8

 

Table 2 :

id      Amount

1       5

2       2

3       8

 

From that, 3 calculated measure :

- based on table 1, SUM(amount) by id (let's call it "sum1")

- based on table 2, SUM(amount) by id (let's call it "sum2")

- based on the 2 previous measure, a "sum3" =  "sum2" - "sum1"

 

The final results by id is :

id      Amount

1       0

2       -8

3       0

 

From that, i'm trying to count the number of time where the result is = 0 (2 times in my case).

 

How can I obtain this result ?

  • Hi,

    Try this measure

    Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]==0))

    or

    Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]=0&&[Solde]<>blank()))

6 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support

    Hi, Thomas_S 

     

    Measure:

     

    Count = COUNTX(FILTER('Table 1',[Sum3]=0),[Sum3])

    Or

    Count = COUNTX(FILTER('Table 1',[Sum3]=0),[ID])

     

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

  • Hi,

    Drag this measure to a card visual

    Measure = countrows(FILTER(VALUES(Allid[id]),[Sum3]=0))

    Ensure that you create a seperate table called Allid which has all unique ID's.  Create a relationship (Many to One and Single) of both your data tables to the Allid table.

    Hope this helps.

    • Thomas_S's avatar
      Thomas_S
      Frequent Visitor

      Thanks, we're close to the result !

       

      With this measure : 

      Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]=0))
       
      I got this result :

       

       

       

      Which is close to correct. 

       

      In my model, the measure's result I try to test is based on a calendar table (need to count the number of days where "solde" = 0). And in this exemple, from range 03/01 to 08/01, there are 6 days. In this filtered selection, the "solde"= 0 is checked the 06/01. But there are no data the 04/01, so, since the day exist in my calendar, I guess the measure consider the "solde"=0 on this particuliar day. And finally count 2 instead of 1.

       

      What condition / filter can I add to ignore days where there are no datas ?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User

        Hi,

        Try this measure

        Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]==0))

        or

        Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]=0&&[Solde]<>blank()))