Forum Discussion
Repeating Amount - DAX Measure
I have 5 tables (Invoices,Credit Notes,Journals,Accounts & Tracking Categories) that are connected with each other with the following DAX measures below.
Now, when I bring the tracking categories as column, it gives me a weird calculation with most of the columns have zero values on them.
Without Tracking Categories - Works fine
With Tracking Categories - most of columns were zero values.
Dax Measures
P&L =
CALCULATE (
SUM ( 'Invoices'[Line Amount Calculation] ),
FILTER (
'Accounts',
Accounts[Class] = "Revenue"
|| Accounts[Class] = "Expense"
)
) - [P&L - Credit Notes] + [P&L (Journals)]
P&L - Credit Notes =
CALCULATE (
SUM ( 'Credit Notes'[Line Amount Credit Note Calculation] ),
FILTER (
'Accounts',
Accounts[Class] = "Revenue"
|| Accounts[Class] = "Expense"
)
)
P&L (Journals) =
CALCULATE (
0 - ( SUM ( 'Journals'[Net Amount] ) ),
FILTER ( 'Journals', 'Journals'[Split] = "JOURNALS" ),
FILTER (
'Accounts',
Accounts[Class] = "Revenue"
|| Accounts[Class] = "Expense"
)
)
2 Replies
ronaldbalza2023 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi Greg_Deckler ,thanks for taking the time on this. Here you go.
1. Sample Data
Accounts Table
Account Type Name Account_UID Revenue Sales 1 Revenue Sales 2 Revenue Interest Income 3 Revenue Interest Income 4 Direct Costs Client Gifts 5 Direct Costs Client Gifts 6 Direct Costs Salary & Wages 7 Direct Costs Salary & Wages 8 Expenses Bank Fees 9 Expenses Bank Fees 10 Expenses Motor Vehicles 11 Expenses Motor Vehicles 12 Expenses Office Expenses 13 Expenses Office Expenses 14 Invoices Table
Line Amount Calculation Account_UID Tracking Categories_ID $6,000 1 1 $1,500 2 2 $250 3 1 $100 4 2 $400 5 1 $50 6 2 $1,000 7 1 $200 8 2 $400 9 1 $200 10 2 $500 11 1 $150 12 2 $250 13 1 $0 14 2 Journals Table
Net Amount Account_UID Tracking Categories_ID $1,500 1 1 $2,000 2 2 $1,500 3 1 $200 4 2 $100 5 1 $50 6 2 $1,500 7 1 $1,500 8 2 $50 9 1 $50 10 2 $500 11 1 $850 12 2 $100 13 1 $250 14 2 Credit Notes Table
Line Amount Credit Note Calculation Account_UID Tracking Categories_ID $500 1 1 $1,000 2 2 $250 3 1 $200 4 2 $0 5 1 $100 6 2 $0 7 1 $100 8 2 $50 9 1 $0 10 2 $0 11 1 $0 12 2 $150 13 1 $0 14 2 Tracking Categories
Tracking Categories_ID Name 1 1 Marquis 2 1 Yabtree 2. Expected Output
Account Type 1 Marquis 1 Yabtree + Revenue $ 10,000 $ 5,000 Sales $ 8,000 $ 4,500 Interest Income $ 2,000 $ 500 + Direct Costs ($ 3,000) ($ 2,000) Client Gifts ($ 500) ($ 200) Salary & Wages ($ 2,500) ($ 1,800) + Expenses ($ 2,000) ($ 1,500) Bank Fees ($ 500) ($ 250) Motor Vehicles ($ 1,000) ($ 1,000) Office Expenses ($ 500) ($ 250) Total $ 5,000 $ 1,500 3. Invoices Table, Credit Notes Table, Journals Table are connected with Accounts Table using Account_UID column. (many to one)
Invoices Table, Credit Notes Table, Journals Table are connected with Tracking Categories Table using Tracking Categories_ID column (many to one)
