cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
plaplant
Helper I
Helper I

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

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?

 

 

plaplant_0-1618414008424.png

 

Regards,

2 ACCEPTED SOLUTIONS
v-yuaj-msft
Community Support
Community Support

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.

v-yuaj-msft_0-1618560898560.png

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

v-yuaj-msft_1-1618561002957.png

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:

v-yuaj-msft_2-1618561238882.png

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.

 

 

 

 

View solution in original post

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.   

View solution in original post

5 REPLIES 5
v-yuaj-msft
Community Support
Community Support

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.

v-yuaj-msft_0-1618560898560.png

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

v-yuaj-msft_1-1618561002957.png

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:

v-yuaj-msft_2-1618561238882.png

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.

 

 

 

 

View solution in original post

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.   

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.   

View solution in original post

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
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors