topic Re: Calculated column Shows Repeated Values in DAX Commands and Tips

Calculated column Shows Repeated Values

Anonymous — Fri, 09 Dec 2022 22:09:43 GMT

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

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

)

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

Re: Calculated column Shows Repeated Values

lbendlin — Sat, 10 Dec 2022 17:32:54 GMT

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

Re: Calculated column Shows Repeated Values

Anonymous — Mon, 12 Dec 2022 18:31:14 GMT

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

Re: Calculated column Shows Repeated Values

lbendlin — Tue, 13 Dec 2022 18:28:25 GMT

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:

Re: Calculated column Shows Repeated Values

Anonymous — Wed, 14 Dec 2022 15:17:46 GMT

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