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

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

Reply
rohan_sharma
New Member

create of simple calculations and visual.

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. 

1 ACCEPTED SOLUTION
Gaga_Jin
Frequent Visitor

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]))

 

  • create clastered chart from power BI visuals section.
  • Drag Date for X-axis and Drag Above measure for Y-axis.

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))

  • use Matrix Visual and drag and drop this two measures.

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]))

 

  • use monthly analyze method, the trend will be shown better.
  • create line chart
  • drag and drop calendar for X-axis and measures for Y-axis.

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]))

 

  • Create Waterfall chart
  • Drag RegionName and DepartmentName to the Category field.
  • Drag ProfitByRegion and ProfitByDepartment to the Values field.

if this helps you, make sure to approve my solution. 

for other tips write me in DM.

View solution in original post

3 REPLIES 3
Gaga_Jin
Frequent Visitor

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]))

 

  • create clastered chart from power BI visuals section.
  • Drag Date for X-axis and Drag Above measure for Y-axis.

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))

  • use Matrix Visual and drag and drop this two measures.

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]))

 

  • use monthly analyze method, the trend will be shown better.
  • create line chart
  • drag and drop calendar for X-axis and measures for Y-axis.

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]))

 

  • Create Waterfall chart
  • Drag RegionName and DepartmentName to the Category field.
  • Drag ProfitByRegion and ProfitByDepartment to the Values field.

if this helps you, make sure to approve my solution. 

for other tips write me in DM.

v-kaiyue-msft
Community Support
Community Support

Hi @rohan_sharma ,

 

Thanks for the reply from @Shravan133 , please allow me to provide another insight: 

 

1. comparison of revenue and expenses by region. 

vkaiyuemsft_0-1722220062152.png

 

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.

vkaiyuemsft_1-1722220118687.png

 

4. waterfall chart showing the breakdown of profit by region and department.

vkaiyuemsft_2-1722220128155.png

 

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.

Shravan133
Solution Sage
Solution Sage

Comparison of Revenue and Expenses by Region

To create a clustered column chart comparing revenue and expenses by region:

  1. Create Measures for Revenue and Expenses by Region:

    RevenueByRegion = CALCULATE( SUM(Transactions[Amount]), Transactions[Type] = "Revenue", ALLEXCEPT(Transactions, Transactions[RegionID]) ) ExpensesByRegion = CALCULATE( SUM(Transactions[Amount]), Transactions[Type] = "Expense", ALLEXCEPT(Transactions, Transactions[RegionID]) )
  2. Build the Clustered Column Chart:

    • Add a clustered column chart to your report.
    • Drag RegionName from the Regions table to the Axis field.
    • Drag RevenueByRegion and ExpensesByRegion measures to the Values field.

2. Calculation of Year-over-Year Growth for Revenue and Profit

To calculate year-over-year (YoY) growth:

  1. Create Measures for YoY Growth:

    RevenueLastYear = CALCULATE( [TotalRevenue], SAMEPERIODLASTYEAR('Date'[Date]) ) RevenueYoYGrowth = DIVIDE( [TotalRevenue] - [RevenueLastYear], [RevenueLastYear] ) ProfitLastYear = CALCULATE( [Profit], SAMEPERIODLASTYEAR('Date'[Date]) ) ProfitYoYGrowth = DIVIDE( [Profit] - [ProfitLastYear], [ProfitLastYear] )
  2. Display YoY Growth:

    • Add a card visual to show the YoY growth percentage for both revenue and profit.

3. Line Chart Showing the Trend of Revenue and Profit Over the Past Three Years

To show a line chart of revenue and profit trends:

  1. Ensure You Have a Date Table:

    • If not, create one to help with time-based calculations.
    Date = CALENDAR(MIN(Transactions[Date]), MAX(Transactions[Date]))
  2. Create Measures for Revenue and Profit Trend:

    RevenueTrend = CALCULATE( [TotalRevenue], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, YEAR) ) ProfitTrend = CALCULATE( [Profit], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, YEAR) )
  3. Build the Line Chart:

    • Add a line chart to your report.
    • Drag Date from the Date table to the Axis field.
    • Drag RevenueTrend and ProfitTrend measures to the Values field.

4. Waterfall Chart Showing the Breakdown of Profit by Region and Department

To create a waterfall chart for profit breakdown:

  1. Create Measures for Profit by Region and Department:

    ProfitByRegion = CALCULATE( [Profit], ALLEXCEPT(Transactions, Transactions[RegionID]) ) ProfitByDepartment = CALCULATE( [Profit], ALLEXCEPT(Transactions, Transactions[DepartmentID]) )
  2. Build the Waterfall Chart:

    • Add a waterfall chart to your report.
    • Drag RegionName and DepartmentName to the Category field.
    • Drag ProfitByRegion or ProfitByDepartment to the Values field.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors