how to get a value in a column to be percent of another value in same column?

[Anonymous]

I have a list of transactions throughout the year and they are categorized. here is an example: 

Transaction	Date	Amount
Credit Card Transaction	1/1/2022	$100
Mortgage Payment	1/1/2022	$1000
Paycheck	1/1/2022	$1000
Credit Card Transaction	2/5/2022	$100
Paycheck	3/1/2022	$1000

How can I get the amount spent on a transaction category (mortgage, credit card transaction, etc) as a percent of paycheck? I want to be able to see this on an annual basis and drill down by month

[sevenhills · Accepted solution]

(May be you gave sample data wrong, but I used and developed below:)

Power Query:

Table1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci5KTcksUXBOLEpRCClKzCtOTC7JzM9T0lEy1DfUNzIwMgIyVQwNDICUs7NSrE60km9+UUl6YnqqQkBiZW5qXgmmWpBimCqwFqDK5IzU5GzsSuGyIKW4HWSkb4rdQUimG+MzPRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Transaction = _t, Date = _t, Amount = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction", type text}, {"Date", type date}, {"Amount", Currency.Type}, {"Category", type text}})
in
    #"Changed Type"

After formatting the date column, Table looks in the data view as below:

Added measure as below:

% per Paycheck = 
var _selCategory = SELECTEDVALUE(Table1[Category])

/* we need to filter as current amount to paycheck amount */
var _payCheckTotal = CALCULATE( sum(Table1[Amount]), Table1[Category] = "Paycheck")
var _TotalBycategory = CALCULATE(sum(Table1[Amount]), Table1[Category] = _selCategory)
var _perc = Divide(_TotalBycategory, _payCheckTotal)

RETURN   if ( _selCategory = BLANK() || _selCategory = "Paycheck", BLANK(), _perc ) 

Output:

Personally, I dont know what exactly you are answering with this ratio as percentage calculation. I provided based on the sample data. 

Hope it helps!