dax expression not working - Purchase Order Value

[ashmitp869]

Hi,

Can someone please suggest 

Data model 

Projects (project_id) = Contract(Project_id) one to many

Contract(contract_id)= Contract_LineItem(contract_id) one to many both
Contract(contract_id)= ContractCustomField(one to one) both

PurchaseOrderValue = 
CALCULATE(
    SUM(ContractCustomField[PurchaseOrderValue]),
    ContractCustomField[IsActive] = TRUE()
   
)

 Not sure with this relationship
Contract(contract_id)= Contract_LineItem(contract_id) one to many both

When WBS is selected the Purchase Order Value is correct which the above related.

But the

whole total Purchase Order Value is Total is not correct.

Please suggest.

[v-ssriganesh]

Hi @ashmitp869,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @ashmitp869,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @ashmitp869,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @Ashish_Mathur & @bhanu_gautam for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Show the problem there clearly.

[ashmitp869]

Hi @Ashish_Mathur 
sharing the sample file.
https://github.com/suvechha/samplepbi/blob/main/sample_info%20-%20po.pbix

Problem -
When export to excel and check the Sum is giving 3569728.95

While report -

[Ashish_Mathur · Accepted solution]

Hi,

I do not know why that is happening but the figure in the visual seems correct.  In the Table view, i filtered the ContractCustomField table on TRUE (ISACTIVE) and 1 (ProjectID).  When i export that table to Excel, i get the same answer as shown in the visual.

[ashmitp869]

Hi ,

I found the reason -which I am having duplicate
Contract(contract_id)= Contract_LineItem(contract_id) one to many both

Contract 500011 is having three Line items in table Contract_LineItem with WBS Code 1131.

I only require one line

i.e 

But when the TOTAL is calculating its having three lines - and thus the TOTAL is different i.e 4,377,206.28.

while export to excel its giving the correct result. But the visual is showing wrong.
Any suggestion, how to handle this

[bhanu_gautam]

@ashmitp869 Try using

DAX
PurchaseOrderValue =
CALCULATE(
SUM(ContractCustomField[PurchaseOrderValue]),
ContractCustomField[IsActive] = TRUE(),
ALL(ContractCustomField)
)

You can use variables to debug and understand the intermediate values in your DAX

DAX
PurchaseOrderValue =
VAR ActiveContracts =
FILTER(
ContractCustomField,
ContractCustomField[IsActive] = TRUE()
)
RETURN
CALCULATE(
SUM(ContractCustomField[PurchaseOrderValue]),
ActiveContracts
)