Forum Discussion
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 data about item transactions:
And then I have a simple calendar:
I'm using the following measure for the matrix:
Sales = IF(CALCULATE(SUM(FactTable[Quantity]), KEEPFILTERS(FactTable[Transaction Type] = "Sale" )) = 0, BLANK(), CALCULATE(SUM(FactTable[Quantity]), KEEPFILTERS(FactTable[TransactionType] = "Sales" )))
And my matrix looks like this:
with the following selections:
Item Number is from the FactTable. Month is from the CalendarTable. And Sales is the mesaure above.
I have a date slicer that allows me to select specific months for viewing. For example, if I choose a date range from January to March, the matrix will display only those three months instead of all twelve.
My question is whether it's possible to add an average column that reflects the date slicer. For instance, if the slicer includes all twelve months, the average calculation should consider all twelve months. In this case, the average for the first row would be 1/12 = 0.083.
If the matrix only shows data for July and August, then the average for the first row would be calculated as 1/2 = 0.5.
Thank you!
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:
2 Replies
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 Felix,
That works really great, thank you! The second formula is what I was looking for.
The only problem I'm having is with the date filter slicer. I have a slicer like the below based on the 'Transaction Date' column, and if I change the slicer, it doesn't update the '_months' variable.
If I use a slicer like the below, based on the 'Month' column, then it does update the '_months' variable.
But I would prefer the first slicer, based on the 'Transaction Date.' Do you know if its possible to make it work like this?
Thank you!
