Forum Discussion
Calculated column Shows Repeated Values
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-p/963216
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