Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello All,
I am working on a measure but im getting repeated values across all the months, can you please assist on what I am missing on the dax commands? Thank you!!
I am using the following measure DAX
Relationships on Layout to Act on Primary Key (PK) and Act to Date2 on Date
Act Table
Layout Table
Statement | Level 1 | Level 2 | Primary Key | Calculation | Table | FROM | TO | Highlight Type | Operator | Main Income Statement Line | Blank Line | Depth |
Income Statement | Revenue | Contracts | 186 | SUM | 186 | 186 | 1 | 1 | TRUE | 2 | ||
Income Statement | Revenue | Materials (Repairs Only) | 187 | SUM | 187 | 187 | 1 | 1 | TRUE | 2 | ||
Income Statement | Revenue | Shipping, Freight | 188 | SUM | 188 | 188 | 1 | 1 | TRUE | 2 | ||
Income Statement | Revenue | Service Repairs | 189 | SUM | 189 | 189 | 1 | 1 | TRUE | 2 | ||
Income Statement | Revenue | Discounts | 190 | SUM | 190 | 190 | 1 | 1 | TRUE | 2 |
There is a difference between a calculated column (calculated once for each row) and a measure (calculated dynamically for each filter context change).
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hello,
Thank you for assisting me on this, see below
Sample file link below:
Desired Outcome
Level 2 | 9/30/2022 | 10/31/2022 |
Contracts | -100000 | -100000 |
Subcontractor expense | 25000 | 50000 |
Gross Profit | -75000 | -50000 |
Current outcome
Tables
Data:
Organization Key | Statement | FSLI | DETAILED | Level 2 | PK | Code | 9/30/2022 | 10/31/2022 | |
1 | Income Statement | Net Revenue | Contracts | Contracts | 186 | 4000 | -100000 | -100000 | |
1 | Income Statement | Cost of Goods Sold | Subcontractor expense | Subcontractor expense | 203 | 5005 | 25000 | 50000 |
Layout Table
Statement | Level 1 | Level 2 | Primary Key | Calculation | Table | FROM | TO |
Income Statement | Revenue | Contracts | 186 | SUM | 186 | 186 | |
Income Statement | Direct Cost of Revenue | Subcontractor expense | 203 | SUM | 203 | 203 | |
Income Statement | Gross Profit | Gross Profit | 222 | CALCULATION | 186 | 203 |
Your Layout[FROM] and Layout[TO] values are numeric. Did you mean to specify date ranges instead?
Replace the item SUBTOTAL field with the Value field:
I am trying to have the Item subtotal formula use the primary Key which is numeric to do the corresponding additions based on the PK from and to so for example PK 186 is revenue, PK 203 is cost of goods sold and PK 222 gross margin should be the addition of the values for PK 186 through PK 203. hope this helps clarify, Thank you for your assistance
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
24 | |
21 | |
21 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
27 | |
23 | |
21 |