cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.
4 REPLIES 4
Highlighted
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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 413 members 4,010 guests
Please welcome our newest community members: