Percentage based off of text value

[toddgack]

Hey All,

I'm very new to Power BI and excel in general. The last two weeks I've gotten a fairly decent handle on some of the easier aspects of Desktop Power BI.

My boss has asked me to add in a chart to a Dashboard of his that shows the percentage of sales made from a prepaid credit card by sales rep.

Seems easy enough... Prepaid / Total Sales 

Axis = Rep Name

Value = Prepaid %

The propblem is that my data colums are all text based and I can't figure out how to get this to work.

I have two colums to base my data off of;

Prepaid 

Status

My Table is "Append 1" 

I need to find the percent of Prepaid "Yes" / Status "Completed"

The best that I've been able to formulate is this Measure

 PrePaid % = ((COUNTROWS(FILTER(Append1,Append1[Prepaid] = "Yes")))/(COUNTROWS(FILTER(Append1,Append1[Status] = "Completed"))))*100

This is returning every sales rep as having 100% prepaid usage.

Can anybody help me figure this out?

[v-shex-msft · Accepted solution]

Hi @toddgack,

>>The problem is that my data columns are all text based and I can't figure out how to get this to work.
You can use value function to convert the text value to number.

According to your description, you can refer to below formula to get the “Prepaid %”:

Table:
 

Measures:

Prepaid % =
var currAmount= if(AND(LASTNONBLANK(Sheet2[Prepaid],Sheet2[Prepaid])="Yes",LASTNONBLANK(Sheet2[Status],Sheet2[Status])="Completed"),VALUE(LASTNONBLANK(Sheet2[Amount],Sheet2[Amount])),0)
return
currAmount/SUMX(FILTER(ALL(Sheet2),Sheet2[Status]="Completed"),VALUE(Sheet2[Amount]))*100

Total Prepaid % = SUMX(FILTER(ALL(Sheet2),AND(Sheet2[Prepaid]="Yes",Sheet2[Status]="Completed")),VALUE(Sheet2[Amount]))/SUMX(FILTER(ALL(Sheet2),Sheet2[Status]="Completed"),VALUE(Sheet2[Amount]))*100

Stacked column chart visual:
 

Card:
 

Regards,
Xiaoxin Sheng