CODEWITHSUNDEEP
powerbidax
Tony
Tony

Reputation: 118

How to insert custom row with dax

The title could have been something else. I am fairly new to Powerbi and I am trying to build a dynamic income statement. The data is imported from sql tables. I have tables for journal entries and chart of account. I dont know how to implement gross profit row (which comes after revenue, COGS before the expenses) and Net Income (after expenses)in the statement.

I managed to create it as a calculated table initially but I soon discovered slicers dont work on calculated tables. can someone point me in the right direction? Is there a way to use slicers on calculated tables or can I somehow do this with the matrix or table visual?

For those not familiar with the jargon

Journal entries table

Account id Balance
1 xxxx
2 xxxx
3 xxxx

COA table

id Name Type
1 Sales Revenue
2 Cost of Sales COGS
3 General Expenses Expense

Expected Income statement visual

Type Balance
Revenue xxxx
COGS xxxx
Gross Profit xxx
Expense xxxx
Net Income xxxx

Gross profit = Revenue - COGS

Net income = Gross profit - Total expense

Upvotes: 0

Views: 738

Answers (1)

Sia
Sia

Reputation: 536

1. Create measures:

Balance Measure = SUM('Journal entries table'[Balance])

Revenue Measure = CALCULATE([Balance Measure],'COA table'[Type]="Revenue")

COGS Measure = CALCULATE([Balance Measure],'COA table'[Type]="COGS")

Expense Measure = CALCULATE([Balance Measure],'COA table'[Type]="Expense")

Gross profit = [Revenue Measure]-[COGS Measure]

Net income = [Gross profit]-[Expense Measure]

2. Create new table:

Table Measure:

Table = DATATABLE("Type",STRING,
 {{"Revenue"},{"COGS"},{"Gross Profit"},{"Expense"},{"Net Income"}})

Add Column:

Balance = 
SWITCH(TRUE(),
    [Type]="Revenue",[Revenue Measure],
    [Type]="COGS",[COGS Measure],
    [Type]="Gross Profit",[Gross profit],
    [Type]="Expense",[Expense Measure],
    [Type]="Net Income",[Net income])

enter image description here

Upvotes: 0

Related Questions