Forum Discussion
Calculated column Shows Repeated Values
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 fo...
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
Current Measures DAX
Actual value = SUM(Act[Value])
item SUBTOTAL =
var currentfrom = selectedvalue(Layout[FROM])
var currentto = selectedvalue(Layout[TO])
return
calculate(
[Actual value],
FILTER(
ALL('Act'),
'Act'[PK]>= currentfrom &&
'Act'[PK]<= currentto
)
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