Forum Discussion

ashmitp869's avatar
ashmitp869
Icon for Responsive Resident rankResponsive Resident
1 year ago
Solved

dax expression not working - Purchase Order Value

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.

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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.

8 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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
    )

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

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

      • ashmitp869's avatar
        ashmitp869
        Icon for Responsive Resident rankResponsive Resident
        1 year ago

        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

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    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.