Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Sum per Week

Hi good day,

I need some help on my calculated column. I want the sum of forecast of each week for both area.

Week1 = North forecast + SouthEast forecast

Week2 = North forecast + SouthEast forecast

Desired Output

Thank you

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi AllanBerces ,

    You can achieve the desired output by creating a new calculated column with this DAX:

    OverallForecast =
VAR CurrentWeek = 'Table'[Week No.]
RETURN
CALCULATE(
    SUM('Table'[Forecast]),
    ALLEXCEPT('Table', 'Table'[Week No.])
)

     

    Your output will look like this:
     

     

4 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi AllanBerces ,

    You can achieve the desired output by creating a new calculated column with this DAX:

    OverallForecast =
VAR CurrentWeek = 'Table'[Week No.]
RETURN
CALCULATE(
    SUM('Table'[Forecast]),
    ALLEXCEPT('Table', 'Table'[Week No.])
)

     

    Your output will look like this:
     

     

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi AllanBerces ,

     

    You can do this on two different ways in Power Query and in DAX.

     

    Power Query:

    • Do a group by week
    • Select a sum of the forecast and all the rows

     

    • Expand the columns Area and forecast from the new column created.

     

     

    DAX:

    • Add the following code to your table:
    OverallForecastDAX = 
        	CALCULATE(
	        	SUM('Table'[Forecast]),
		        'Table'[Week] = EARLIEST('Table'[Week]),
		        REMOVEFILTERS(
			        'Table'[Area],
			        'Table'[Forecast]
		            )
	        )

     

     

    Please see attach PBIX file.

     

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago
    HIi AllanBerces Please try the below dax to get your result

    Overall_Forecast =
        SUMX(
            FILTER(
                Region,
                Region[Week No] = EARLIER(Region[Week No])
            ),
            Region[Forecast]
        )