DAX - Is it something for SUMX ? Cannot make it work

[Vander1981]

Hi All,

I have got a problem

My data model is the following one : Calendar Table; Product Table; Geography Table; Sales Table.

I created a measure that does the work at the exception of the Grand Total of my Pivot which display a sales extrapolation for each Business area.

DIVIDE ( [Total Sales] ; [Achieved Days] ) * Working Days

What I would like to achieve is that at the Grand total, the measure sums up all the extrapolations at business area level.

I tried every possibility with SUMX but could not solve this.

Thanks a lot for your help.

 

[gazpage]

The pattern for aggregating at an already aggregated level is:

SUMX(
    VALUES(Table[BusinessArea],
    CALCULATE("Your measure here")
)

You need a column with business areas in it that you can enter in the first part. The formula says:

1. Iterate over the list of unique values in the Business Area column
2. The CALCULATE part converts this from a row context to a filter context so that at each row of the iteration all the related items are visible to the measure
3. Calculate your measure for each business area
4. Sum up the results (could just as well be a max or average or something)

Measures come with their own implicit CALCULATE, so you can actually skip it if you use a measure rather than a formula.

 

[Vander1981]

Thank you Gazpage for this really valuable explanation.

It worked just fine !

My error was that I didn't use the CALCULATE part