cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vbourbeau Regular Visitor
Regular Visitor

Average on Matrix Subtotals

Hi BI expert,

I try to do something who are probably obvious

I have a matrix with 2 line where we can drag down. l want to put the average of the total sales.

Ex:

Client        Sales      Average

Client1      2000$       50%

Client2      1000$       25%

Client3      1000$       25%

Total         4000$       100%

 

And if we drag down

 

Client        Part      Sales      Average

Client1         1        1000$       50%

                    2          500$       25%

                    3          500$       25%

                 Total      2000$      50%

Client2                   1000$       25%

Client3                   1000$       25%

Total                       4000$       100%

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
vbourbeau Regular Visitor
Regular Visitor

Re: Average on Matrix Subtotals

Also I need to add 

ISFILTERED function to be able to change the ALLEXCEPT depend of the hierarchy I'm in.

View solution in original post

4 REPLIES 4
bhpage Member
Member

Re: Average on Matrix Subtotals

I'm not sure I understand your goal here. Could you elaborate further?

 

Thanks,

Ben

vbourbeau Regular Visitor
Regular Visitor

Re: Average on Matrix Subtotals

Ok,

 

If you look the first example I want to add the last column the average of sales. my client 1 sale for 2000$ what is 50% of my total sales. How can I add this Column?

 

Also if I drag down in the matrix I have each part sale for a specific client. I want to have the average of each part on the client sales. the part 1 sale 1000$ what is 50% of the total sales of this client.

vbourbeau Regular Visitor
Regular Visitor

Re: Average on Matrix Subtotals

ok look what I've done.

I create a new measure who keep the total sales for all the matrix

Total locked = calculate([Total]; ALLEXCEPT(table;somefilter))
 
And I divide
Average sales = divide([total];[total locked]
 
I got this:

Client        Sales      Average

Client1      2000$       50%

Client2      1000$       25%

Client3      1000$       25%

Total         4000$       100%

 

But when I drill down

Client        Part      Sales      Average

Client1         1        1000$       25%

                    2          500$       12.5%

                    3          500$       12.5%

                 Total      2000$      50%

Client2                   1000$       25%

Client3                   1000$       25%

Total                       4000$       100%

 

It's probably normal, but what I want is to have the average of the drill down value on the drill down total and not on the total sales. 

 

Client1         1        1000$       50%

                    2          500$       25%

                    3          500$       25%

 

 I want when I look for one client what is the percentage of sales for part 1 on is total sale to him not to all client.

vbourbeau Regular Visitor
Regular Visitor

Re: Average on Matrix Subtotals

Also I need to add 

ISFILTERED function to be able to change the ALLEXCEPT depend of the hierarchy I'm in.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 54 members 1,193 guests
Please welcome our newest community members: