Cumulative Value calculate with Dax Measure

[Anonymous]

Hello Experts!

I try to calculate the cumulative Headcounts, and the  Calculation is sucessed when i try to caluclate without Dax measure, as below:

Cumulative HC = CALCULATE(sum(Headcount[Headcount]),FILTER(all('Working days'),'Working days'[Month]<= MAX('Working days'[Month])))

But When i try to use a DAX instead of Sum, it is not workintg :

the DAX is to calculate effective Headcount: 

Effective HC = DIVIDE( [Toal Working Hours], [Working hrs Value]*sum('Working days'[Working days]) )

The Cumulative Effective headcount as 

Cumulative effective HC = CALCULATE([Effective HC], FILTER(all('Working days'),'Working days'[Month]<= MAX('Working days'[Month])))

Then It returns table like average  headcount

Is because the DAX measure? T.T

Please help out, thanks!

BR

[Jihwan_Kim · Accepted solution]

Hi,

I am not quite sure whether I fully understood your second question, but are you trying to create a table? SUMX function does not show Table as a result, but a single value. SUMMARIZE function does show table as a result.

[Jihwan_Kim]

Hi,

Please try something like below.

Cumulative effective HC =
CALCULATE (
    SUMX (
        SUMMARIZE ( 'TableName', 'TableName'[Cost Center], 'Working days'[Month] ),
        [Effective HC]
    ),
    FILTER (
        ALL ( 'Working days' ),
        'Working days'[Month] <= MAX ( 'Working days'[Month] )
    )
)

[Anonymous]

Hello! 

thanks a lot, it really works. 

But i try to understood the logic, so i breakdown the formula

In below formula, it is not working

So i edit it like this below, then the table column "Effective" returns correct value. 

Could you tell why is like this? thanks in advance!

[Anonymous]

Hello!

Yes, you are right, i shouldnt use SUMX as a table..

but to understand the formula you wrote: 

SUMX ( SUMMARIZE ( 'TableName', 'TableName'[Cost Center], 'Working days'[Month] ), [Effective HC] )

does it means  the Expression "[Effective HC] start to work, when meet the filter context (cost center, month) created from Summarize function? 

[Jihwan_Kim · Accepted solution]

Hi,

In case only [Effective HC] measure is used inside CALCULATE, I think it will not cumulatively sum, because the measure [Effective HC] itself is created by using divide function. If it needs to be seen as cumulative sum, then I think SUMX function has to be written together with proper virtual table.

I think the below measure may be is written in a more easy way to read.

Cumulative effective HC =
CALCULATE (
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( 'TableName', 'TableName'[Cost Center], 'Working days'[Month] ),
            "@effectiveHC", [Effective HC]
        ),
        [@effectiveHC]
    ),
    FILTER (
        ALL ( 'Working days' ),
        'Working days'[Month] <= MAX ( 'Working days'[Month] )
    )
)

[Anonymous]

Now i understood, thanks alot 🙂

[Jihwan_Kim · Accepted solution]

Hi,

I am not quite sure whether I fully understood your second question, but are you trying to create a table? SUMX function does not show Table as a result, but a single value. SUMMARIZE function does show table as a result.