Create Table from Measure to be used in further calculations

[Anonymous]

I have a measure that displays the following when I plug it into a Matrix. 

I want to be able to use the values displayed in a separtate calculation, but plugging the measure in directly doesn't work since the measure needs these other rows and columns to filter properly. 

I can export the visual to give me a csv with the correct numbers, but I want to avoid exporting and reintegrating a "new source" just to get the numbers back into PowerBI.

[v-saisrao-msft]

Hi @Anonymous,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @Anonymous,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[johnt75 · Accepted solution]

How you approach it depends on what you're trying to do with the values. If you only need to perform the calculation for a certain combination, or combinations, then could store it in a variable like

VAR MyValue =
    CALCULATE (
        [My Measure],
        TREATAS (
            { ( "London", "Tops" ), ( "Paris", "Shoes" ) },
            'Store'[Location],
            'Product'[Category]
        )
    )

If you wanted to do sum of all the values, you could do something like

My Measure 2 =
VAR MyValue =
    SUMX (
        SUMMARIZE ( 'Table', 'Store'[Location], 'Product'[Category] ),
        [My Measure]
    )
RETURN
    MyValue

[Anonymous]

I will eventually want to average the results in each column for each category.

The original measure is based on conditions. For example, if a specific condition is true, calculate the measure, else place a hardcoded value.

I've noticed when I drill up to the Category level, only the "calculated" measure values are taken into account and the hardcoded values are not. I want all the values in a column, calculated or hardcoded, to be averaged for a given category. 

[v-saisrao-msft]

Hi @Anonymous,
I wanted to check if you had the opportunity to review the information provided by @johnt75 , @some_bih . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[johnt75]

You want the second version of the code I posted, but replace SUMX with AVERAGEX.

Make sure to include all the columns in the SUMMARIZE which you have in the matrix visual.

[some_bih]

Hi @Anonymous please check link for CALCULATETABLE function in DAX

[Anonymous]

I'm not exactly sure how to implement this with a measure. Eventually, I want to average all the values in a column for a given category.