Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
hello, guys. i'm new here and i hope i will find solution on this platform. unfortunately, power BI is new for me. i just learn some simple dax formulas, which helped to achieve some goals, but because of short time issue i need your help.
because of data sensitivity i can't share them fully, but i can give you guys some information. i have 3 Table.
1. Transactions: TranartmentID, RegionID, Amount and type (Rev or epx)
2. Departments: DepartmentID and Department Name.
3. Regions: RegionID and RegionName.sactionID, Date, Dep
i already created relationship between this tables and create measures (easy ones).
1. TotalRevenue = CALCULATE(SUM(Transactions[Amount]), Transactions[Type] = "Revenue")
2. TotalExpenses = CALCULATE(SUM(Transactions[Amount]), FinancialTransactions[Type] = "Expense")
3. Profit = [TotalRevenue] - [TotalExpenses].
4. Margin = DIVIDE([TotalRevenue] - [TotalExpenses], [TotalRevenue])
but now i want to create:
1. comparison of revenue and expenses by region. (maybe with clastered chart)
2. Calculation of Year-over-Year growth for revenue and profit
3. line chart showing the trend of revenue and profit over the past three years
4. waterfall chart showing the breakdown of profit by region and department.
if other information is necessary, let me notify.
thank you guys in advance.
Solved! Go to Solution.
hi , @rohan
To simplify the matter and it is accepted in practice, it is better to create a date table separately, which you will connect to your main table (transactions). To do this, use the function of creating a new table and use the following Dax code:
Calendar =
ADDCOLUMNS (
CALENDAR (Max(Transaction[Date])), Min(Transaction[Date])),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthName", FORMAT([Date], "MMM YYYY")
)
After creating of Calendar Table, create a relationship between Transaction Table and it.
1. Dax code for Expenses and Revenues By region:
RevenueByRegion = CALCULATE([TotalRevenue], ALLEXCEPT(Regions, Regions[RegionName]))
ExpensesByRegion = CALCULATE([TotalExpenses], ALLEXCEPT(Regions, Regions[RegionName]))
2. Calculation of Year-over-Year growth for revenue and profit
Dax:
YoYRevenueGrowth = CALCULATE([TotalRevenue], DATEADD(Calendar[Date], -1, YEAR))
YoYProfitGrowth = CALCULATE([Profit], DATEADD(Calendar[Date], -1, YEAR))
3. line chart showing the trend of revenue and profit over the past three years:
Dax:
MonthlyRevenue = CALCULATE([TotalRevenue], DATESMTD(Calendar[Date]))
MonthlyProfit = CALCULATE([Profit], DATESMTD(Calendar[Date]))
4. waterfall chart showing the breakdown of profit by region and department.
Dax:
ProfitByRegion = CALCULATE([Profit], ALLEXCEPT(Regions, Regions[RegionName]))
ProfitByDepartment = CALCULATE([Profit], ALLEXCEPT(Departments, Departments[DepartmentName]))
if this helps you, make sure to approve my solution.
for other tips write me in DM.
hi , @rohan
To simplify the matter and it is accepted in practice, it is better to create a date table separately, which you will connect to your main table (transactions). To do this, use the function of creating a new table and use the following Dax code:
Calendar =
ADDCOLUMNS (
CALENDAR (Max(Transaction[Date])), Min(Transaction[Date])),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthName", FORMAT([Date], "MMM YYYY")
)
After creating of Calendar Table, create a relationship between Transaction Table and it.
1. Dax code for Expenses and Revenues By region:
RevenueByRegion = CALCULATE([TotalRevenue], ALLEXCEPT(Regions, Regions[RegionName]))
ExpensesByRegion = CALCULATE([TotalExpenses], ALLEXCEPT(Regions, Regions[RegionName]))
2. Calculation of Year-over-Year growth for revenue and profit
Dax:
YoYRevenueGrowth = CALCULATE([TotalRevenue], DATEADD(Calendar[Date], -1, YEAR))
YoYProfitGrowth = CALCULATE([Profit], DATEADD(Calendar[Date], -1, YEAR))
3. line chart showing the trend of revenue and profit over the past three years:
Dax:
MonthlyRevenue = CALCULATE([TotalRevenue], DATESMTD(Calendar[Date]))
MonthlyProfit = CALCULATE([Profit], DATESMTD(Calendar[Date]))
4. waterfall chart showing the breakdown of profit by region and department.
Dax:
ProfitByRegion = CALCULATE([Profit], ALLEXCEPT(Regions, Regions[RegionName]))
ProfitByDepartment = CALCULATE([Profit], ALLEXCEPT(Departments, Departments[DepartmentName]))
if this helps you, make sure to approve my solution.
for other tips write me in DM.
Hi @rohan_sharma ,
Thanks for the reply from @Shravan133 , please allow me to provide another insight:
1. comparison of revenue and expenses by region.
2. Calculation of Year-over-Year growth for revenue and profit.
YoYRevenueGrowth =
VAR _PreviousYearRevenue =
CALCULATE (
SUM ( 'Transactions'[Amount] ),
FILTER (
ALL ( 'Transactions' ),
YEAR ( 'Transactions'[Date] )
= YEAR ( MAX ( 'Transactions'[Date] ) ) - 1
&& 'Transactions'[Type] = "Revenue"
)
)
VAR _CurrrentYearRevenue =
CALCULATE (
SUM ( 'Transactions'[Amount] ),
FILTER (
ALL ( 'Transactions' ),
YEAR ( 'Transactions'[Date] ) = YEAR ( MAX ( 'Transactions'[Date] ) )
&& 'Transactions'[Type] = "Revenue"
)
)
RETURN
DIVIDE ( _CurrrentYearRevenue - _PreviousYearRevenue, _PreviousYearRevenue )
YoYProfitGrowth =
VAR _PreviousYearRevenue =
CALCULATE (
SUM ( 'Transactions'[Amount] ),
FILTER (
ALL ( 'Transactions' ),
YEAR ( 'Transactions'[Date] )
= YEAR ( MAX ( 'Transactions'[Date] ) ) - 1
&& 'Transactions'[Type] = "Revenue"
)
)
VAR _CurrrentYearRevenue =
CALCULATE (
SUM ( 'Transactions'[Amount] ),
FILTER (
ALL ( 'Transactions' ),
YEAR ( 'Transactions'[Date] ) = YEAR ( MAX ( 'Transactions'[Date] ) )
&& 'Transactions'[Type] = "Revenue"
)
)
VAR _PreviousYearExpense =
CALCULATE (
SUM ( 'Transactions'[Amount] ),
FILTER (
ALL ( 'Transactions' ),
YEAR ( 'Transactions'[Date] )
= YEAR ( MAX ( 'Transactions'[Date] ) ) - 1
&& 'Transactions'[Type] = "Expense"
)
)
VAR _CurrrentYearExpense =
CALCULATE (
SUM ( 'Transactions'[Amount] ),
FILTER (
ALL ( 'Transactions' ),
YEAR ( 'Transactions'[Date] ) = YEAR ( MAX ( 'Transactions'[Date] ) )
&& 'Transactions'[Type] = "Expense"
)
)
VAR _PreviousYearProfit = _PreviousYearRevenue - _PreviousYearExpense
VAR _CurrrentYearProfit = _CurrrentYearRevenue - _CurrrentYearExpense
RETURN
DIVIDE ( _CurrrentYearProfit - _PreviousYearProfit, _PreviousYearProfit )
3. line chart showing the trend of revenue and profit over the past three years.
4. waterfall chart showing the breakdown of profit by region and department.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
To create a clustered column chart comparing revenue and expenses by region:
Create Measures for Revenue and Expenses by Region:
Build the Clustered Column Chart:
To calculate year-over-year (YoY) growth:
Create Measures for YoY Growth:
Display YoY Growth:
To show a line chart of revenue and profit trends:
Ensure You Have a Date Table:
Create Measures for Revenue and Profit Trend:
Build the Line Chart:
To create a waterfall chart for profit breakdown:
Create Measures for Profit by Region and Department:
Build the Waterfall Chart:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
32 | |
15 | |
14 | |
13 | |
9 |