Can you have different calculations for each column of a Matrix?

[plaplant]

Hi, I've only been using PowerBI for the last few weeks and have been asked to look at an existing dashboard.  Below is an image that shows a matrix in their dashboard.  Currently it appears there is a subtotal for each Program that appears to just be an average of all the teams under that program.   For example, Corp Information and Technolo... has 6 teams with values of 80, 52, 73, 82, 79 and 82 and a Team Score column.   The subtotal then shows a value of 75 which is the average.   Team Stability is another column that shows as  a %.   I'm wondering if you can have unique formulas for each column versus doing an average for all.  For example, can I have an average for the %'s but then have a sum (or other formula) for the other values?    Is this possible and if so how is that accomplished?

Regards,

[Anonymous · Accepted solution]

Hi @plaplant ,

Yes! You can do some steps as follows.

1. For some simple senarios (sum or average of total table), you can change the type of aggregation in each field.

2. You can also choose the percentage of values in "Show value as".

3. For some special senarios, just like "I would like to show the average of each category", you can create some measures. I did a test. The result is here.

TEST_1 =
IF (
    ISINSCOPE ( Sheet2[Category] ),
    DIVIDE (
        SUMX (
            FILTER ( ALL ( Sheet2 ), [Category] = SELECTEDVALUE ( Sheet2[Category] ) ),
            [Value]
        ),
        SUMX (
            FILTER ( ALL ( 'Sheet2' ), [C2] = SELECTEDVALUE ( Sheet2[C2] ) ),
            [Value]
        )
    ),
    DIVIDE (
        SUMX ( FILTER ( ALL ( Sheet2 ), [C2] = SELECTEDVALUE ( Sheet2[C2] ) ), [Value] ),
        CALCULATE ( SUM ( 'Sheet2'[Value] ), ALL () )
    )
)

TEST_2 = AVERAGE(Sheet2[Value])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @plaplant ,

Yes! You can do some steps as follows.

1. For some simple senarios (sum or average of total table), you can change the type of aggregation in each field.

2. You can also choose the percentage of values in "Show value as".

3. For some special senarios, just like "I would like to show the average of each category", you can create some measures. I did a test. The result is here.

TEST_1 =
IF (
    ISINSCOPE ( Sheet2[Category] ),
    DIVIDE (
        SUMX (
            FILTER ( ALL ( Sheet2 ), [Category] = SELECTEDVALUE ( Sheet2[Category] ) ),
            [Value]
        ),
        SUMX (
            FILTER ( ALL ( 'Sheet2' ), [C2] = SELECTEDVALUE ( Sheet2[C2] ) ),
            [Value]
        )
    ),
    DIVIDE (
        SUMX ( FILTER ( ALL ( Sheet2 ), [C2] = SELECTEDVALUE ( Sheet2[C2] ) ), [Value] ),
        CALCULATE ( SUM ( 'Sheet2'[Value] ), ALL () )
    )
)

TEST_2 = AVERAGE(Sheet2[Value])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[plaplant]

Thank you, I will review this in more detail and test out but was curious on something else as well.   If, in my matrix, I have a field that is just notes imported from a spreadsheet, how can I display the notes but then eliminate the total JUST for this one column.   The other columns would be the sum's or the averages but a total doesn't make sense with a note column like this.  I'm wondering how I can simply bring in the notes without a total being displayed as the matrix tries to apply something there.   

[plaplant · Accepted solution]

Please disregard this part of the question.  I found that I can simply use 

SELECTEDVALUE(my field) and then I can add that into my matrix in place of the other field.   This removes that total at the bottom.   

[Anonymous]

Hi @plaplant ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.

Best Regards,

Yuna

[amitchandak]

@plaplant , Isinscope and value check can help

refer:https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

and  https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

or

 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.