Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
(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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |