Forum Discussion

zeelongtime's avatar
zeelongtime
Frequent Visitor
4 years ago
Solved

Problem with Grand Total not totalling all the values

Hi there,

I need help calculating the grand total correctly on the Allocation Column.

 

The idea is to get the 4 Mo Average to populate everytime the 202001 Allocation Tons is 0, 

The formula I have in as follow which provide what I want at the cell level but total is wrong.

=IF(HASONEVALUE(Data[202001 Allocation Tons]),SUM(Data[4 Months Average]),SUM([202001 Allocation Tons]))

 

Thanks

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  zeelongtime ,

    You can create a measure, this is used to solve the Total error:

    Measure=
VAR __table = SUMMARIZE('Table', 'Table'[202001 Allocation Tons] ,"__value",[Allocation])
RETURN
IF(HASONEVALUE('Table'[202001 Allocation Tons]), [Allocation],SUMX(__table,[__value]))

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • zeelongtime's avatar
      zeelongtime
      Frequent Visitor
      4 years ago

      Hi Liu, Thanks for taking the time.

      This is not working for me, formula doesnt include the 4Mo Avg and the Allocation is a calculated field I would like to get, but I see that you included on your formulas.

       

      Am I reading this correctly or this formula should be created after my initial calculation?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  zeelongtime ,

    Sorry, I read the wrong information, you try to use this dax:

    Measure=
VAR __table = SUMMARIZE(Data, Data[202001 Allocation Tons] ,"__value", [202001 Allocation Tons])
RETURN
IF(HASONEVALUE(Data[202001 Allocation Tons]), Data[4 Months Average],SUMX(__table,[__value]))

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.