Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Incorrect Grand Total

Hi all, 

 

I have this DAX formula to basically calculate sum of amount for months after selected date. 

 

Show Future Forecast =

var a = SUM(Data[Amount])
var b = MAX(Data[Month for Finacial Date])
var c = MAX(Data[Year for Finacial Date])
RETURN
if (b <= SELECTEDVALUE(DimDate[MonthNum]) && c = SELECTEDVALUE(DimDate[Year]) , BLANK(), a)
 
This is what it returns. The Totals for Amount and the Totals for Show Future Forecast are the same but it shouldn't be since Show Future Forecast is missing two months. Is anyone able to assist and advise why?

 

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    4 years ago

    Hey Anonymous ,

     

    Forecast and Amount are the same in the Total Line, as there is no active filter for year month in the total.

    You can solve this using the table iterator function SUMX SUMX – DAX Guide.

    Your measure then will be similar to this:

    Show Future Forecast = 
SUMX(
    SUMMARIZE(
        '<yourcalendartable>'
        , '<yourcalendartable>'[year]
        , '<yourcalendartable>'[month]
    )
    , ... // the numeric expression
)

    The numeric expression might look like this:

    var a = CALCULATE( SUM(Data[Amount]) )
var b = CALCULATE( MAX(Data[Month for Finacial Date]) )
var c = CALCULATE( MAX(Data[Year for Finacial Date]) )
RETURN
if (b <= SELECTEDVALUE(DimDate[MonthNum]) && c = SELECTEDVALUE(DimDate[Year]) , BLANK(), a)

    The CALCULATE is necessary to transform the existing ROW context that has been created by the table iterator function SUMX into a filter context.

     

    Hopefully, this provides what you are looking for to tackle this challenge.

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Tom,

       

      Thanks for that. That makes sense. Unfortunately, i'm still getting the same totals even with the addition of the SUMX function. This is what i'm using now. Is there something incorrect i'm doing here?

       


      SUMX(
      SUMMARIZE(
      'DimDate'
      , 'DimDate'[Year]
      , 'DimDate'[Month]),

      var a = CALCULATE( SUM(Data[Amount]) )
      var b = CALCULATE( MAX(Data[Month for Finacial Date]) )
      var c = CALCULATE( MAX(Data[Year for Finacial Date]) )
      RETURN
      if (b <= SELECTEDVALUE(DimDate[MonthNum]) && c = SELECTEDVALUE(DimDate[Year]) , BLANK(), a))

       

       

       

      • TomMartens's avatar
        TomMartens
        Super User
        4 years ago

        Hey Anonymous ,

         

        please create a pbix file that contains sample data but still reflects your data model (tables, relationships between tables, calculated columns, and measures). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method share the xlsx as well.
        Please explain the expected result based on the sample data you provided.

         

        Regards,

        Tom