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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 

 

 

 

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.   

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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