Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

 

 

 

 

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.   
Anonymous
Not applicable

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

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.