Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mpsg5513
New Member

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

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

TransactionDateAmount
Credit Card Transaction1/1/2022$100
Mortgage Payment1/1/2022$1000
Paycheck1/1/2022

$1000

Credit Card Transaction2/5/2022

$100

Paycheck3/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

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

(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:

sevenhills_0-1670461678226.png

 

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:

sevenhills_1-1670461761290.png

sevenhills_2-1670461769848.png

 

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!

 

 

View solution in original post

1 REPLY 1
sevenhills
Super User
Super User

(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:

sevenhills_0-1670461678226.png

 

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:

sevenhills_1-1670461761290.png

sevenhills_2-1670461769848.png

 

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!

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.