Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Summarize multiple variables

Hi,   I need to calculate the standard hours per MFG_ORDER_NAME. This calculated value per individual line is OK, but the sum is incorrect. I tried summarize function but I need help ons this.   ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    I would probably go with something like:

     

    Measure Total =
VAR __table = SUMMARIZE('Table',[MFG_ORDER_NAME],[DEPARTMENT],[BASIS],"__hrsStandard",[HRS STANDARD])
VAR __total = SUMX(__table,[__hrsStandard])
RETURN
IF(HASONEVALUE([DEPARTMENT]),[HRS STANDARD],__total)

    You may have to modify the SUMMARIZE.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

Write this additional measure and drag this to the Table visual

 

=IF(HASONEVALUE(MFG_ORDER_NAME),[HRS_STANDARD],SUMX(SUMMARIZE(VALUES([MFG_ORDER_NAME]),[MFG_ORDER_NAME],"ABCD",[HRS_STANDARD]),[ABCD]))

 

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Ashish,

     

    Tried following extra measure and dragged it into the visual. Still total is incorrect

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

       

      Share the link from where i can download your PBI file.  Also, show the expected result in a tet box there.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Ashish,

         

        Hereby the link and expected result. I rebuild the Power BI file to a document based on excel. The original file is a file based on direct query on our data warehouse which I cannot share with underlying data.

         

        This is the link: https://drive.google.com/open?id=13X8wROZ0tyUlwI3uahNAZK_Y0u7EtVSy