Forum Discussion

AndyDo's avatar
AndyDo
Frequent Visitor
1 year ago
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 ...
Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    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:

    https://community.fabric.microsoft.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591

     

     

AndyDo's avatar
AndyDo
Frequent Visitor
1 year ago

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!