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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors