Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
)
)
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |