Adding a column which calculates % of the two other columns

[Gisela]

Hi, I am new at PowerBI and could not find an answer to this probably easy question.

I have the table above

I want to add a column which show the % of "Ttl rcvd new"/"Total sent"

"Ttl rcvd new" & "Total sent" are  from different excel sheets

I have tried to add a measure to calculate the % but 1/ I don't know where to add the measure 2/ i get an error or it just showed 100% for both.

How can I add the % column?

Thank you,

Gisela

[SivaMani]

Hi @Gisela,

Create a calculated column with divide function,

% Column = Divide(Ttl rcvd new,Total sent)

[Gisela]

hi @SivaMani,

thank you for your quick response. I am very new to this so couldn't figure it out. Here is what I did:

I added a calculated column as per above but the %s are not correct. In the table, I have "Total sent" but that one is not showing up as an option too choose in my calculated column so I used Ttl sent new instead: Ttl sent new = CALCULATE(COUNT(Sent[Survey type]))

I changed the "SHOW VALUE AS" to "Percent of grand total" but maybe that is not correct? i don't know where the 78.46% is coming from... I tried both SUM and COUNT but none give correct %. The first one should be 367/906 = 40.50%

This is the measure behind the "Ttl rcvd new": Ttl rcvd new = CALCULATE(COUNT(Rcvd[Survey #]))

This is the measure behind the "Ttl sent": Total sent = COUNT(Sent[Survey type])

I tried a few others and they gave the same result. 

I had this one first: Total rcvd = COUNT(Rcvd[Survey type])   but it doesn't show up when I tried to create the calculated column.

Another one I tried was: Ttl rcvd new2 = SUM(Rcvd[Survey type])

[Gisela]

I added the calculated column in the SENT table.

[SivaMani]

@Gisela

Could please share some sample data of the tables?

sample pbix file would be better

[Gisela]

@SivaMani

https://www.dropbox.com/s/dz37dej16es8q5f/RCVD%20sample%20data%20PBI.pbix?dl=0

https://www.dropbox.com/s/375m5gpj69uk5pd/SENT%20sample%20data%20PBI.pbix?dl=0

I hope I did this correctly... First time attaching a PBI file.

Thank you!

[SivaMani · Accepted solution]

@Gisela,

https://www.dropbox.com/s/8ci6vtcqhjkl6sy/RCVD%20sample%20data%20PBI.pbix?dl=0

Let me know if anything needed

Regards,

Siva

[Gisela]

THANK YOU.

I will test this now with my real data and if I have questions, I will let you know and otherwise, set it to solved.

I really, really appreciate the help.

Gisela

[SivaMani]

@Gisela,

It's my pleasure .

[Gisela]

Almost there, two more questions:

1. how do I display the number as a %. I.e. 54% instead of 0.54

2. The % in the TOTAL, show the total of the two numbers above (in your example, it shows 66.87% which is the total of 35.29% and 31.58% but i should be 12/26 = 18%, i.e. show 18%.

Thank you @SivaMani!

[Gisela]

Hi @SivaMani

I finally managed to change it to show as % so that part is done.

1. SOLVED! how do I display the number as a %. I.e. 54% instead of 0.54

2. UNSOLVED: The % in the TOTAL, show the total of the two numbers above (in your example, it shows 66.87% which is the total of 35.29% and 31.58% but i should be 12/26 = 18%, i.e. show 18%.

[SivaMani]

@Gisela,

For that case,

You have to convert the calculated column as a measure.

Just create a calculated measure with same DAX.

Regards,

Siva

[Gisela]

One more which I created in order to be able to properly create a relationship between the other two tables:

https://www.dropbox.com/s/vt1xsw1g46afpf3/Survey%20look%20up%20table%20sample%20data%20PBI.pbix?dl=0