POWER BI
Financial Analysis
USING DAX QUERIES
By - ABHISHEK PANDEY
Index
Introduction Key indications
What is “POWER BI?” Queries
Problem statement Conclusion
Data overview Thanks
Introduction
You are a Financial Data Analyst tasked with analyzing credit card usage and
financial metrics for a banking institution. Using the provided data, you will
create reports in Power BI by applying DAX functions.
Your goal is to calculate financial metrics like running totals, moving averages,
and growth rates, and generate KPIs that assess customer behavior, credit
utilization, and delinquency risk. The analysis will provide key insights for
improving customer retention and financial performance.
What is
POWER BI
Power BI is a business intelligence and data visualization tool developed by
Microsoft. It is designed to help businesses transform raw data from various sources
into meaningful and interactive insights. Power BI is a collection of software services,
apps, and connectors that work together to turn your unrelated sources of data into
coherent, visually immersive, and interactive insights
Data Analysis Expressions (DAX) is a powerful formula language used in Power BI,
Power Pivot in Excel, and SQL Server Analysis Services (SSAS). DAX is designed to
work with relational data and allows users to create custom calculations and
aggregations on data models.
Problem statement
Running Total of Credit Card Transactions Delinquency Rate: Calculate the percentage of clients with
Calculate the 4-week moving average of the creditLimit for Delinquent_Acc > 0.
each client. Credit Risk Score: Create a score for each client based on
Calculate the mom% growth and wow% growth on their Avg_Utilization_Ratio, Delinquent_Acc, and
transaction amount. Total_Revolving_Bal.
Calculate Customer Acquisition Cost (CAC) as a Ratio of Income vs Credit Limit Correlation: Show the correlation
Transaction Amount. between Income and Credit_Limit for all clients.
Calculate the yearly average of avg_utilization_ratio for all Average Customer Satisfaction Score by Credit Card
clients. Category: Calculate the average Cust_Satisfaction_Score
Calculate the percentage of Interest_Earned compared to by Card_Category.
Total_Revolving_Bal for each client. Loan Approval vs Credit Limit: Analyze how Credit_Limit
Calculate Top 5 Clients by Total Transaction Amount. affects Personal_loan approval by calculating the average
Identify clients whose Avg_Utilization_Ratio exceeds 80%. credit limit for clients with and without loans.
Customer Churn Indicator: Create a KPI that flags clients High Risk Clients Flag: Create a flag for clients whose
who have not made any transactions (Total_Trans_Amt = 0) Total_Revolving_Bal exceeds 90% of their Credit_Limit and
in the last 6 months. who have a high Avg_Utilization_Ratio.
Dataset Overview Dataset Link
This analysis consists Credit_card and Customer as separate tables in
this data-set, which will be used to analyze Credit’s Customer behavior ,
potential risk factors.
Credit_card Customer
Key columns : Key columns :
Credit_Limit Personal_loan
Total_Revolving_Bal Customer_Job
Avg_Utilization_ratio Income
Interest_Earned Cus_Satisfaction_Score
Objective : Objective :
Tracking spending habits Overall Consumer satisfaction
Credit utilization Potential churn
To avoid risk factors (Delinquent acc) Forming better policies
Cleaning Dataset...
Necessary steps :
Although the dataset is cleaned and is ready to use, but
there are still some steps that are better to be performed in
order to smoothly progress towards the analysis.
Append queries :
The dataset initially consisted 4 tables.
Credit_card, cc_add & Customers, Customers_add
Both the additional tables shall be appended into their respective tables
as they contain additional rows of data, which is better to be appended
for much sophisticated way.
Standardizing columns before appending tables:
All the columns didn’t had same Column title , as it would have became
hurdle while appending tables.
Credit_card contained column named Total_Trans_Vol and cc_add
contained Total_Trans_Ct , which was later renamed as
Total_Trans_Vol to ensure standard columns titling while appending
both tables.
Key indications
Financial metrics : Risk indicators :
Running totals Delinquency rates,
Moving averages Credit risk scores
Growth rates for High-risk flag
transactions. clients
Correlations: Behavioral Insights:
Examined income vs. Retention
Credit limit Churn indicators
Loan approvals vs. Credit Transaction
limits. patterns.
Queries
Running total of Credit_Card
Transactions.
Why calculate running total?
A running total is a cumulative sum that keeps track of the ongoing sum of a
series of numbers or values.
It allows you to see the total value at each step as you progress through the
data.
A running total helps you see how values add up over time, making it easier to
track trends.
Visualizing running totals with charts can provide clearer insights into your
data.
4-week moving average of the
creditLimit for each client.
Why calculate moving average?
A moving average (MA) is a stock indicator commonly used to help smooth out
price data by creating a constantly updated average price.
the impacts of random, short-term fluctuations on the price of a stock over a
specified time frame are mitigated.
A rising moving average indicates that the security is in an uptrend, while
declining moving average indicates a downtrend.
Month Over Month growth in
percentage ( % ).
Why calculate Month over Month growth?
Month-over-month growth is a key metric used in business analytics to measure the
change in performance over consecutive months. It provides valuable insights into the
trends and patterns of a business, allowing professionals to make informed decisions and
drive growth.
Month-over-month growth measures the rate of change in the value of a metric, such
as revenue or active users, on a monthly basis, expressed as a percentage of the prior
month’s value.
Week Over Week growth in
percentage ( % ).
Why calculate Week over Week growth?
Week over week growth is a powerful function used by business professionals,
analysts, and marketers to track and measure performance trends over consecutive
weeks.
During weekly performance reviews to assess the impact of recent strategies.
After launching a new product or campaign to evaluate its immediate effects.
To identify and act on short-term patterns or trends.
To compare different weeks to understand seasonality or external influences.
For quickly assessing the impact of operational changes or market shifts.
Customer Acquisition Cost (CAC) as
a ratio of transaction amount.
Why calculate customer acquisition cost?
Customer Acquisition Cost (CAC) is a metric that allows companies to measure the cost of acquiring
a new customer.
The metric involves considering all the expenses incurred while convincing customers to
purchase products or services a business offers.
This includes time and investment made on research, marketing, and advertising. Ultimately,
enabling businesses to calculate the ROI of customer acquisition.
If it’s closer to 1:1, that means you’re spending just as much money on attaining customers as
they’re spending on your products or services. If it’s higher than 3:1, like 4:1 that means you need to
spend more on sales and marketing and could be missing out on opportunities to attract new
leads.
Average of avg_utilization_ratio
for all clients ( Yearly ).
Why calculate Yearly average utilization ratio?
Credit Utilization (AUR) often refers to the ratio of used credit to the total available credit.
Tracking the yearly average can:
Assess overall credit health and usage trends.
Help maintain a good credit score by ensuring credit usage stays within optimal limits
(e.g., below 30% of total credit).
Aid in budgeting and financial planning by identifying patterns in borrowing behavior.
By summarizing the utilization patterns over a year, stakeholders can make informed,
data-driven decisions that optimize performance, reduce costs, and enhance
sustainability.
% of Interest_Earned compared to
Total_Revolving_Bal for each client.
Why calculate percentage of interest earned?
The percentage provides a clear insight into how much revenue is being
generated from the client relative to their outstanding balance. It’s a key metric
for profitability analysis, risk management, and strategic planning in financial
institutions.
Measure Profitability
Assess Client Credit Behavior
Evaluate Risk Levels
Strategic Decision-Making
Top 5 Clients by Total
Transaction Amount.
Why calculate Top 5 clients by Total Transaction Amount?
Identifying your top 5 customers based on sales within each region gives you a clearer, more actionable
view of your business’s performance. This allows you to tailor your approach, focus on specific
markets, and strategically allocate resources where they matter most.
Helps to reframing the policies being more effective.
Giving some perks to clients who have spent most amount.
Clients whose
Avg_Utilization_Ratio exceeds
80%.
Steps to calculate Firstly, we created a new measure which
clients whose avg categorizes “avg_utilization_ratio” into
two parts where the ratio is equal to or
utilizations above 80% as “Exceeds” and below 80%
ratio exceeds 80% :- as “Not exceeds”
After that, we created a new table
which filtered down the rows where
the “Avg_utilization_ratio” is equal to
or more than 80%.
Calculating the number of clients
whose Average Utilization Ratio (AUR)
exceeds 80% can provide valuable
insights for strategic decision-
making, resource management, and
risk assessment.
Customer Churn
Indicator
Steps to calculate
clients whose avg
utilizations
ratio exceeds 80% :-
Firstly, we created a new
column which categorized
where “Total transaction
amount” was blank for last 6
months as “Churned” which
means customers have
churned and other as “Not
churned”.
After that, created a new measure which calculates the number of rows which contains
“Churned” from the new columns which we created earlier.
we have used a Card to portray the number of customers who have Churned.
The Customer Churn Indicator is a metric used to identify and predict the likelihood of
customers discontinuing their relationship with a business.
Calculating and analyzing this indicator is critical for organizations focused on retaining
customers, improving customer satisfaction, and maximizing revenue.
Losing customers directly impacts revenue. By monitoring churn indicators, businesses can
take steps to retain customers and reduce the financial impact of churn, which is often
more cost-effective than acquiring new customers.
Delinquency Rate
Delinquency rate refers to the percentage of loans within a financial
Why calculate institution's loan portfolio whose payments are delinquent. When
analyzing and investing in loans, the delinquency rate is an important
“Delinquency metric to follow
Rate?” It's a vital metric for lenders, offering insights into credit risk and
portfolio performance.
Credit Risk Score
Steps to The Credit Risk Score is a vital metric in financial and lending industries,
serving multiple purposes that benefit both lenders and borrowers.
calculate Assessing Borrower's Creditworthiness
Determining Loan Terms
“Credit risk Firstly, we created a new column which contains the Normalized revolving
balance value from Total_Revolving_Bal which was standardized as
score” :- Avg_Utilization_ratio and Delinquency_ratio values.
After that, we created another new column which
contain the sum of “Avg_Utilisation_Ratio”
product by 0.5 , “Delinquent_Acc” product by
0.3 and “Normalised_revolving_balance”
product by 0.2 ( All the ratios are decided by
organization )
It is necessary to find out risk score for multiple
reasons such as Mitigating Financial Loss,
Efficient Resource Allocation, Regulatory
Compliance.
Income vs Credit Limit Correlation
In this analysis, we utilized Quick We calculate correlation to measure
Measures to calculate the correlation the strength and direction of a linear
between different metrics. relationship between two variables.
Average customer satisfaction
score by Credit Card Category
Calculating the average customer satisfaction
Why calculate score by credit card category allows businesses
Average customer to optimize their product offerings, improve
customer experiences, and ultimately increase
satisfaction score?
loyalty and profitability.
Average Credit Limit
( Without LOAN )
Calculating the Average Credit Limit (with Loan)
Why calculate
is useful in various financial contexts, especially
Average Credit for individuals, financial institutions, or
Limit businesses, to assess creditworthiness,
evaluate financial stability, and make informed
( With LOAN )? financial decisions.
Average Credit Limit
( Without LOAN )
Why calculate Calculating the Average Credit Limit (Without
Average Credit Loan) is particularly useful in understanding
the available revolving credit of an individual
Limit or a business, excluding fixed-term obligations
( Without LOAN )? like loans.
High Risk Clients Flag
Firstly, we created a new column which
Steps to calculate categorized “Normalised_revolving_balance”
High Risk Clients above “0.9” and “Avg_Utilization_ratio” above
“0.8" as “Flagged” and below those as “Not
Flag :- flagged”
After that we created a new measure which counted rows which contained “Flagged”
customers as value, hence we got our number of Flagged customers.
Benefits of High-Risk Client Flagging
1. Proactive Loss Mitigation: Reduces bad debts and fraud.
2. Improved Decision-Making: Helps allocate resources efficiently.
3. Regulatory Compliance: Avoids fines and legal issues.
By calculating and acting on high-risk client flags, businesses and institutions can
improve their financial performance while protecting themselves from potential risks.
Conclusion
The analysis provided deep insights into customer spending, identifying
patterns and usage trends that inform better financial management and credit
policies.
Key metrics, calculated with DAX in Power BI, highlighted significant factors
such as high-risk segments, credit utilization rates, and delinquency risk.
Actionable insights emerged to support strategies for reducing credit defaults,
optimizing credit offerings, and improving customer retention. These findings
enable the bank to enhance customer satisfaction and loyalty, while aligning
with goals for sustainable growth and risk management.
Thanks!
Socials
Gmail
abhishekpandeyofficiall@[Link]
LinkedIn
Abhishek pandey
