Reply
Regular Visitor
Posts: 29
Registered: ‎10-11-2017
Accepted Solution

Matrix - How to avoid Total and Subtotal aggregation for a field

Hello Team,

 

I am working on a report that has a requirement that i want to create a matrix with following information.

 

Rows

1. State

2. Region

3. City     

Values

1. Customers

2. Billed

3. % Billed 

 

% Billing is measure calcualted based on the formulat ( # Billed/ #customers ) in percentage. I would like to have the total and subtotals for Customers, Billed value field in matrix. But for % Billed, I don't want to see the total and subtotal aggregation based on Rows.

 

Please help me in how to avoid the total and subtotal only for % Billed measures. I know we can either have total and subtotal for all values or avoid for all values. but ,i dont know how to avoid for a value fields.

 

I would appreciate any suggestions on resolving this issue.

Capture.PNG

 

 

 

 

 

 

 

 

 

Thank you!!


Accepted Solutions
Highlighted
Community Support Team
Posts: 5,709
Registered: ‎09-21-2016

Re: Matrix - How to avoid Total and Subtotal aggregation for a field

Hi @manojsv16,

 

Please refer to below measure:

Bill% = IF(ISINSCOPE('Test Data'[City]),SUM('Test Data'[Bill])/SUM('Test Data'[Customer]))

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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


All Replies
Highlighted
Community Support Team
Posts: 5,709
Registered: ‎09-21-2016

Re: Matrix - How to avoid Total and Subtotal aggregation for a field

Hi @manojsv16,

 

Please refer to below measure:

Bill% = IF(ISINSCOPE('Test Data'[City]),SUM('Test Data'[Bill])/SUM('Test Data'[Customer]))

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.