Forum Discussion
Solved
Add an average column to a matrix visualization
Hi, I have a matrix visualiazion used to display sales data based on item transactions. The matrix shows the data based on calendar months. For example, I have the following Fact Table with ...
Hi AndyDo ,
Do you want to present the total and the average value?
If it's just the average without doing a lot of changes you can create the following measure:Matrix value = var _value =SUM('Table'[Value]) var _months = COUNTROWS(ALLSELECTED('Table'[month])) Return IF(ISINSCOPE('Table'[month]), _value,FORMAT( DIVIDE(_value, _months), "0.00"))If you want to have the total you can do this:
Matrix value = var _value =SUM('Table (3)'[Value]) var _months = COUNTROWS(ALLSELECTED('Table (3)'[month])) Return IF(ISINSCOPE('Table (3)'[month]), _value,FORMAT( _value ,"#,###") & " | " & FORMAT( DIVIDE(_value, _months), "0.00"))Be aware that there are other options were you can create an hibrid table that will get the values but the syntax is much more complex check this example:
Hi AndyDo ,
Do you want to present the total and the average value?
If it's just the average without doing a lot of changes you can create the following measure:
Matrix value =
var _value =SUM('Table'[Value])
var _months = COUNTROWS(ALLSELECTED('Table'[month]))
Return
IF(ISINSCOPE('Table'[month]), _value,FORMAT( DIVIDE(_value, _months), "0.00"))
If you want to have the total you can do this:
Matrix value =
var _value =SUM('Table (3)'[Value])
var _months = COUNTROWS(ALLSELECTED('Table (3)'[month]))
Return
IF(ISINSCOPE('Table (3)'[month]), _value,FORMAT( _value ,"#,###") & " | " & FORMAT( DIVIDE(_value, _months), "0.00"))
Be aware that there are other options were you can create an hibrid table that will get the values but the syntax is much more complex check this example: