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
Roinka
Helper I
Helper I

% per row and not per total

Hi there,

I have a matrix which shows the no of participants per category per type.

Category  | Type 1  |  Type 2 | Type 3

categoryA, 20, 40, 40                                                                          --(total 100 participants)

categoryB, 10,160,30                                                                          --(total 200 participants)

 

I am using a measure which holds the NoOfParticipants which is the CountRows of the table. The measure is auto   filtered by the category and type in the matrix.

 

I would like to present this as a % out of a total per row, so in above example we will get:

Category  | Type 1  |  Type 2 | Type 3

categoryA, 20%, 40%, 40%                                                                          --(total 100% participants)

categoryB, 5%,80%,15%                                                                              --(total 100% participants)

 

How do i create the second matrix?

Thank you.

1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

EDIT: I completely misread the question.  You can just use the Show Value As option in the dropdown of the measure in the matrix options to select Row Total.  This gives the results you want without any complicated measures.

 snipa.PNG

 

I'm leaving my previous answer below, just in case you find it interesting.

 

If you could share your current COUNTROWS measure, that would be incredibly helpful as a starting point for us.  

 

It seems likely that you can to use an expression like this that uses ISINSCOPE to determine if it is being calculated as a regular value or as a column total in a matrix.

NoOfParticipants for Matrix = 
IF( 
    ISINSCOPE(Table1[Type]), 
    [NoOfParticipants], 
    DIVIDE(
        [NoOfParticipants], 
        CALCULATE([NoOfParticipants], ALLSELECTED(Table1) ) 
    )*100 
)

 

View solution in original post

2 REPLIES 2
Cmcmahan
Resident Rockstar
Resident Rockstar

EDIT: I completely misread the question.  You can just use the Show Value As option in the dropdown of the measure in the matrix options to select Row Total.  This gives the results you want without any complicated measures.

 snipa.PNG

 

I'm leaving my previous answer below, just in case you find it interesting.

 

If you could share your current COUNTROWS measure, that would be incredibly helpful as a starting point for us.  

 

It seems likely that you can to use an expression like this that uses ISINSCOPE to determine if it is being calculated as a regular value or as a column total in a matrix.

NoOfParticipants for Matrix = 
IF( 
    ISINSCOPE(Table1[Type]), 
    [NoOfParticipants], 
    DIVIDE(
        [NoOfParticipants], 
        CALCULATE([NoOfParticipants], ALLSELECTED(Table1) ) 
    )*100 
)

 

jthomson
Solution Sage
Solution Sage

Divide the first measure by itself, but in the second one use calculate and allexcept whatever the type field is

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.