Compute percenteage of total by each category in matrix table

[natureorigin01]

Hi,

I am trying to replicate a pivot table like this in power BI

This is what I have achieved so far with the Matrix table and it does not really work because I cannot compute the percentage of total for each status by each Source (For example, ATP has 7 leads on 13/05/2020 and 4 leads were approved, then the percentage for approved under ATP should be 57% not 7.27% like in the picture) , it only has the percentage of row, percentage of column and percentage of grand total which are not really what I want, please help me with this case, thank you!

[amitchandak]

@natureorigin01 , Not clear. There is % of total you can use for every column.

Or refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[natureorigin01]

Hi,

I have found a way to compute the percentage of each Status under each Source, but now the percentage of each Source is 100%,

I need it to be the percentage of the column total, please advise if you can, thank you!

https://ibb.co/PzP1Cpz

[tex628]

Hi, 

I'd advice you to start by creating a measure counting to total count per source. 

It should look something along the lines of:

Measure = Calculate([Lead count] ; Removefilter([Status]))

And when you place it in the matrix it should return something along the lines of:

approved	4	7
busy		7
callback consult		7
closed		7
rejected	2	7
trash	1	7

You should then finally be able to use this measure in a divide with [lead count] to get the correct %. 

Let me know how it goes,

/ J

[natureorigin01]

Hi,

Thanks for your reply,

I tried the methods and it returned the percentage for the statuses under the source but the percentage of the source is now 100%, I need it to be the percentage of the column total, is there anyways to achieve this?

[tex628]

Try this, 

Measure = 
IF( Selectedvalue([Status]) <> BLANK();
Calculate([Lead count] ; Removefilter([Status])) ; 
Calculate([Lead count] ; Removefilter([Source])) 

/ J

[natureorigin01]

Thanks for your reply,

I tried the suggested code but it returned some weird numbers, not the column total, for example on 13 May 2020, the column total is 559 but it returned 1675

[tex628]

Do you have any visual level filters on the table that affect the "Source" column? 

[natureorigin01]

Yes, I have put a filter to not include 'Delivered' and Blank Sources

[tex628]

Measure = 
IF( Selectedvalue([Status]) <> BLANK();
Calculate([Lead count] ; Removefilter([Status])) ; 
Calculate([Lead count] ; Removefilter([Source]) , [Source] <> BLANK() , [Source] <> "Delivered") 

/J

[natureorigin01]

Thanks for your reply, 

Now I have removed all the filters but the blank Source and some other Sources like ELB and EVC are not showing the correct total, is there anyways to fix this?

[tex628 · Accepted solution]

My bad, try this:

Measure = 
IF( Hasonefilter([Status]) ;
Calculate([Lead count] ; Removefilter([Status])) ; 
Calculate([Lead count] ; Removefilter([Source]) , [Source] <> BLANK() , [Source] <> "Delivered")

[natureorigin01]

It worked! 

Thanks so much!