Forum Discussion
Count and Sum
Hi All,
Consider below table as example
Consider below table
| Project ID | Met | Not Met | Grand Total | Condition = If (Met = Grand Total), 1,0) |
| 10030-01 | 2 | 2 | 1 | |
| 10077-03 | 5 | 5 | 1 | |
| 10093-02 | 8 | 8 | 1 | |
| 10098-01 | 9 | 9 | 1 | |
| 1010-01 | 42 | 42 | 1 | |
| 10119-01 | 13 | 13 | 1 | |
| 10142-01 | 5 | 5 | 1 | |
| 10162-01 | 5 | 2 | 7 | 0 |
| 10310-02 | 7 | 7 | 1 | |
| 10331-04 | 8 | 8 | 1 | |
| 10352-04 | 2 | 2 | 4 | 0 |
| 10376-01 | 12 | 12 | 1 | |
| 10379-02 | 5 | 5 | 1 |
I want to have a dax which will compare the Met with Grand Total and then if they both matches then it is 1 or else it is 0
once it is done i want to count the ' 1' and then total as sum
In my data set there is month column as well which is connected to dim_date table and that i can take care once the mesure is bulit
HI Anonymous,
You can try to use the following measure formulas if it suitable for your requirement:
Measure = VAR summary = SUMMARIZE ( ALLSELECTED ( 'Table' ), [Project ID], "Met", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Met/NotMet] = "Met" ), "Total", COUNTROWS ( 'Table' ) ) RETURN COUNTROWS ( FILTER ( summary, [Met] = [Total] ) )Regards,
Xiaoxin Sheng
7 Replies
Tahreem24Super User
Anonymous ,Try this Measure:
Measure = CALCULATE(COUNTROWS(TableA),FILTER(TableA,TableA[Met]=TableA[Grand Total]))+0- Tahreem24
Anonymous Refer this screen shot also :
- Tahreem24
Anonymous , Try this steps:
Step 1: First Create a Measure for Met and likewise for NotMet:
Met Measure = Caluculate(COUNTROWS(TableName),FILTER(TableName,TableName[Met/NotMet Column]="Met")
Step 2: Likewise create for NotMet. Measure.
Step 3: Create Measure for Grand Total.
Grand Total Measure= COUNTROWS(TableName)
Step 4: Create a Measure like below:
Measure = CALCULATE(COUNTROWS(TableName),FILTER(TableName,[Met Measure]=[Grand Tota Measure]))+0