Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SUMMARIZE Not Returning Correct Sum Value

Hello,   I'm trying to perform a SUM of the Production Qty at the Scheduled Shift level which is the next level of granularity from MATERIAL_NUMBER but I'm not getting the correct summation. I perf...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Using the "Group By" functionality in Power Query Editor worked for me. I followed the instructions near the bottom of this article link...Link 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous 

 

That's what I had originally but the records inflate as the ALLEXCEPT() function ignores any relationships configured in the data model and creates a foreach combination result.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

That makes sense.

Can you post the formula you entered using SUMMARIZE to derive the 'Production Qty' measure?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Here's the DAX behind the measure...

     

    Production Qty (Date-PlantNum-PlantType-ProdLine-Shift) =
    SUMX(
    SUMMARIZE('HANA Production Fact','HANA Production Fact'[Scheduled Date], 'HANA Production Fact'[Plant], 'HANA Production Fact'[COS Production Line], 'HANA Production Fact'[Scheduled Shift],"Production Qty 1",CALCULATE(SUM('HANA Production Fact'[Production Quantity]))),[Production Qty 1])
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Maybe this will work...

       

      CALCULATE(
      SUM('HANA Production Fact'[Production Quantity]),
      ALLEXCEPT(HANA Production Fact,
      'HANA Production Fact'[Scheduled Date],
      'HANA Production Fact'[Plant],
      'HANA Production Fact'[COS Production Line],
      'HANA Production Fact'[Scheduled Shift]
      )
      )

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        You DAX proposal includes ALLEXCEPT() which will inflate my records. Unfortunately, this method won't work. I'm going to try the "Group By" functionality within the Power Query Editor and see if that gives me the desired results. I appreciate your efforts in trying to find a solution here!