Forum Discussion
Dax Formula to Count while excluding 0 values
Hi All,
I am trying to find the equivilent to the below sum function, however removing 0 values.
Rev = Calculate(SUM(Table[Column]),'Table 2'[Column 2]="Filter")
Count= Calculate(SUM(Table[Column]),'Table 2'[Column 2]="Filter") ----Would I add in COUNT ------then, "<>0"?
Hi Anonymous ,
To calculate the sum of a column in Power BI and remove 0 values, you can use the following formula:
Rev = CALCULATE(SUM(Table[Column]), 'Table 2'[Column 2] = "Filter", Table[Column] <> 0)This formula uses the `CALCULATE` function to filter the table based on the condition `'Table 2'[Column 2] = "Filter"` and then sums the values in the `Table[Column]` column that are not equal to 0.
If you want to count the number of non-zero values in the same column, you can use the following formula:
Count = CALCULATE(COUNT(Table[Column]), 'Table 2'[Column 2] = "Filter", Table[Column] <> 0)This formula uses the `CALCULATE` function to filter the table based on the condition `'Table 2'[Column 2] = "Filter"` and then counts the number of values in the `Table[Column]` column that are not equal to 0.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
To calculate the sum of a column in Power BI and remove 0 values, you can use the following formula:
Rev = CALCULATE(SUM(Table[Column]), 'Table 2'[Column 2] = "Filter", Table[Column] <> 0)This formula uses the `CALCULATE` function to filter the table based on the condition `'Table 2'[Column 2] = "Filter"` and then sums the values in the `Table[Column]` column that are not equal to 0.
If you want to count the number of non-zero values in the same column, you can use the following formula:
Count = CALCULATE(COUNT(Table[Column]), 'Table 2'[Column 2] = "Filter", Table[Column] <> 0)This formula uses the `CALCULATE` function to filter the table based on the condition `'Table 2'[Column 2] = "Filter"` and then counts the number of values in the `Table[Column]` column that are not equal to 0.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.