create of simple calculations and visual.

[rohan_sharma]

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. 

[Gaga_Jin · Accepted 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]))

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

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.

[Shravan133]

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.