How to build a matrix using a measure (not a field) as a value?

[wpf_]

So i have created a matrix before populating fields from a table.  But I feel the matrix I need to build is a different circumstance.  It uses the fields as the row and column, but the value I need a measure for it.  I tried it but the value is coming back wrong.  

I have:   AvgSpeed = CALCULATE(AVERAGE(TableA[SPEED]), TableB[ID]=1234))  but it shows the same avg speed for every hour and for all locations. 

Here are diagrams that might better explain what I have and what I need in the matrix:

Table A and B have relationship using ID as primary. 

So with a matrix I want to show avg speed (value) of each month, each hour, for each location.  Furthermore, the avg speeds are filtered by direction (northbound, southbound).  

Hope someone can help. Thanks.

[v-shex-msft · Accepted solution]

Hi @wpf_,

You can try to use the following measure formula if it suitable for your scenario:

AvgSpeed =
CALCULATE (
    AVERAGE ( TA[SPEED] ),
    ALLSELECTED ( TA ),
    VALUES ( TA[Date] ),
    VALUES ( TA[Hour] ),
    VALUES ( TB[Location] ),
    VALUES ( TB[Direction] )
)

If above not help, please share some dummy data with the expected result to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[wpf_]

@v-shex-msft ,

That worked! thank you.  

Even thought it worked i am still having a hard time understanding it.  I will need to read up on dax from the ground up.  Can you explain to me step by step what your expression do?  I have a feeling calculate is a transition context?  So AVERAGE gets me the avg of the column i want.  What does ALLSELECTED and VALUES do?  Thanks. 

AvgSpeed =
CALCULATE (
    AVERAGE ( TA[SPEED] ),
    ALLSELECTED ( TA ),
    VALUES ( TA[Date] ),
    VALUES ( TA[Hour] ),
    VALUES ( TB[Location] ),
    VALUES ( TB[Direction] )
)

[v-shex-msft]

Hi @wpf_,

If you are interested in these functions, you can take a look at the following blog of these function usages:

The definitive guide to ALLSELECTED 

DAX – The Many Faces of VALUES() 

Regards,

Xiaoxin Sheng

[mahoneypat]

You should be able to do the following to get your desired result.

1.  Make a relationship between A and B (Many:1) on the ID column

2. Add a calculated column to TableA for the Month with this  Month = FORMAT(TableA[Date], "mmmm")

3. Make a Matrix visual with Month and Hour columns in the rows and the Direction column in the column field well

4. Make a measure like this    AverageSpeed = AVERAGE(TableA[Speed])   and put it in the Values area

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[wpf_]

@mahoneypat ,

I tried that but it's showing the same value for each hour.