Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
crisaldana1
New Member

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

 

crisaldana1_1-1670623045605.png

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 

crisaldana1_2-1670623126677.png

Act Table

crisaldana1_3-1670623353079.png

 

Layout Table

 
StatementLevel 1Level 2Primary KeyCalculationTableFROMTOHighlight TypeOperatorMain Income Statement LineBlank LineDepth
Income StatementRevenueContracts186SUM 18618611TRUE 2
Income StatementRevenueMaterials (Repairs Only)187SUM 18718711TRUE 2
Income StatementRevenueShipping, Freight188SUM 18818811TRUE 2
Income StatementRevenueService Repairs189SUM 18918911TRUE 2
Income StatementRevenueDiscounts190SUM 19019011TRUE 2

 

4 REPLIES 4
lbendlin
Super User
Super User

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

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

 

Desired Outcome

Level 29/30/202210/31/2022
Contracts-100000-100000
Subcontractor expense2500050000
Gross Profit-75000-50000
   
 

Current outcome

crisaldana1_0-1670868733968.png

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 KeyStatementFSLIDETAILEDLevel 2PKCode9/30/202210/31/2022 
1Income StatementNet RevenueContractsContracts1864000-100000-100000 
1Income StatementCost of Goods SoldSubcontractor expenseSubcontractor expense20350052500050000 

 

Layout Table

StatementLevel 1Level 2Primary KeyCalculationTableFROMTO
Income StatementRevenueContracts186SUM 186186
Income Statement Direct Cost of RevenueSubcontractor expense203SUM 203203
Income StatementGross ProfitGross Profit222CALCULATION186203

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:

 

lbendlin_0-1670956063562.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors