Forum Discussion

Anonymous

Not applicable

3 years ago

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...

Super User

3 years ago

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

Anonymous

Not applicable

3 years ago

Hello,

Thank you for assisting me on this, see below

Sample file link below:

https://we.tl/t-qD5iFsibY3?utm_campaign=TRN_TDL_05&utm_source=sendgrid&utm_medium=email&trk=TRN_TDL_05

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

lbendlin
Super User
3 years ago
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:
- Anonymous
  Not applicable
  3 years ago
  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