DAX for Sum of values in each cell by count of values excluding 0

[Anonymous]

HI 

I'm working on logic to combine multiple columns each cell value to derive single column, detials are 

To calculate sum of values in each cell by count of values excluding Zero 

So logically in below table 

• For first row -
  • Sum of values : 0+0+0+0+0=0 
  • Count of values : 0
  • Solution --> 0/0 = 0 
• Second row -
  • Sum of values : 4+5+5+4+5=23 
  • Count of values : 5
  • Solution --> 23/5 = 4.6
• Third row -
  • Sum of values : 5+0+0+5+5=15 
  • Count of values : 3
  • Solution --> 15/3 = 5
• Fourth row -
  • Sum of values : 0+5+5+0+5=15 
  • Count of values : 3
  • Solution --> 15/3 = 5

For Sum of row it should calculate sum of values at row level for multiple columns defined

For count of rows it should calculate count of each cell excluding 0 then consider rest values as 1.(example for third row and for all 5 columns it should be 1+0+0+1+1=3)

Product1_Rating	Product2_Rating	Product3_Rating	Product4_Rating	Product5_Rating
0	0	0	0	0
4	5	5	4	5
5	0	0	5	5
0	5	5	0	5

You can download PBIX file from below link -

@ Power BI File Download

[Anonymous · Accepted solution]

Hi @Anonymous  Try this on a calculated column.

Column = 
VAR Ratings = {
    Products[Product1_Rating],
    Products[Product2_Rating],
    Products[Product3_Rating],
    Products[Product4_Rating],
    Products[Product5_Rating]
}
VAR S =
    SUMX ( Ratings, [Value] )
VAR NonzeroRatings =
    COUNTX ( FILTER ( Ratings, [Value] > 0 ), [Value] )
RETURN
    DIVIDE(S, NonzeroRatings, 0)

Hope this helps.

Appreciate a kudos.

Please mark as solution if this resolves your problem.

[Anonymous]

Thanks Everyone above both queries worked 

Kudos to you 

[Greg_Deckler]

@Anonymous  - So basically you are looking at a multi-column aggregation with some filtering of 0 values. BTW the first line you would need to use DIVIDE with the third parameter to avoid a divide by zero error. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-p/391698#M129

You *may* wish to consider unpivoting your rating columns and the whole thing would potentially be much easier.

[nandic]

@Anonymous ,

1) create calculated column to sum values per each row: 

Row Sum Column = 'Table'[Product1_Rating]+'Table'[Product2_Rating]+'Table'[Product3_Rating]+'Table'[Product4_Rating]+'Table'[Product5_Rating]

2) create calculated column to count ratings higher that 0:

Row Count Column = IF('Table'[Product1_Rating]>0,1,0) + IF('Table'[Product2_Rating]>0,1,0) + IF('Table'[Product3_Rating]>0,1,0) + IF('Table'[Product4_Rating]>0,1,0) + IF('Table'[Product5_Rating]>0,1,0)

3) Divide these two columns: 

Result = DIVIDE('Table'[Row Sum Column],'Table'[Row Count Column])