Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Experts!
I try to calculate the cumulative Headcounts, and the Calculation is sucessed when i try to caluclate without Dax measure, as below:
Is because the DAX measure? T.T
Please help out, thanks!
BR
Solved! Go to 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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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!
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?
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] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Now i understood, thanks alot 🙂
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |