Calculated column Shows Repeated Values

Question

Hello All,&nbsp;I am working on a measure but im getting repeated values across all the months,&nbsp; can you please assist on what I am missing on the dax commands? Thank you!!&nbsp;I am using the following measure DAXitem SUBTOTAL = var currentfrom = selectedvalue(Layout[FROM])var currentto = selectedvalue(Layout[TO])returncalculate(&nbsp; &nbsp; [Actual value], &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALL('Act'),&nbsp; &nbsp; &nbsp; &nbsp; 'Act'[PK]&gt;= currentfrom &nbsp;&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'Act'[PK]&lt;= currentto&nbsp; &nbsp; ))&nbsp;Relationships on Layout to Act on Primary Key (PK) and Act to Date2 on Date&nbsp;Act Table&nbsp;Layout Table&nbsp;StatementLevel 1Level 2Primary KeyCalculationTableFROMTOHighlight TypeOperatorMain Income Statement LineBlank LineDepthIncome StatementRevenueContracts186SUM&nbsp;18618611TRUE&nbsp;2Income StatementRevenueMaterials (Repairs Only)187SUM&nbsp;18718711TRUE&nbsp;2Income StatementRevenueShipping, Freight188SUM&nbsp;18818811TRUE&nbsp;2Income StatementRevenueService Repairs189SUM&nbsp;18918911TRUE&nbsp;2Income StatementRevenueDiscounts190SUM&nbsp;19019011TRUE&nbsp;2&nbsp;

lbendlin · Answer

There is a difference between a calculated column (calculated once for each row) and a measure (calculated dynamically for each filter context change).
&nbsp;
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 · Answer

Hello,Thank you for assisting me on this, see below&nbsp;Sample file link below:https://we.tl/t-qD5iFsibY3?utm_campaign=TRN_TDL_05&amp;utm_source=sendgrid&amp;utm_medium=email&amp;trk=TRN_TDL_05&nbsp;Desired OutcomeLevel 29/30/202210/31/2022Contracts-100000-100000Subcontractor expense2500050000Gross Profit-75000-50000&nbsp;&nbsp;&nbsp;&nbsp;Current outcomeCurrent Measures DAXActual value = SUM(Act[Value])item SUBTOTAL = var currentfrom = selectedvalue(Layout[FROM])var currentto = selectedvalue(Layout[TO])returncalculate(&nbsp; &nbsp; [Actual value],&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALL('Act'),&nbsp; &nbsp; &nbsp; &nbsp; 'Act'[PK]&gt;= currentfrom &nbsp;&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'Act'[PK]&lt;= currentto&nbsp; &nbsp; )&nbsp;Tables&nbsp;Data:Organization KeyStatementFSLIDETAILEDLevel 2PKCode9/30/202210/31/2022&nbsp;1Income StatementNet RevenueContractsContracts1864000-100000-100000&nbsp;1Income StatementCost of Goods SoldSubcontractor expenseSubcontractor expense20350052500050000&nbsp;&nbsp;Layout TableStatementLevel 1Level 2Primary KeyCalculationTableFROMTOIncome StatementRevenueContracts186SUM&nbsp;186186Income Statement&nbsp;Direct Cost of RevenueSubcontractor expense203SUM&nbsp;203203Income StatementGross ProfitGross Profit222CALCULATION186203

lbendlin · Answer

Your Layout[FROM] and Layout[TO] values are numeric.&nbsp; Did you mean to specify date ranges instead?
Replace the item SUBTOTAL field with the Value field:
&nbsp;

&nbsp;

anonymous · Answer

I am trying to have the Item subtotal formula use the primary Key which is numeric&nbsp; to do the corresponding additions based on the PK from and to so for example&nbsp; &nbsp;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

Forum Discussion

Calculated column Shows Repeated Values

4 Replies

Related content

TMDL - calculated column

DAX help calculating Repeat Customers

Paginated Report headers not repeating

Calculated Column Value Result Repeating

Direct Lake Model - Calculated Column/Tables

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

Level 2	9/30/2022	10/31/2022
Contracts	-100000	-100000
Subcontractor expense	25000	50000
Gross Profit	-75000	-50000

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