Forum Discussion
Total Measures issue - within a Matrix
Hi Anonymous ,
Try this:
Measure = VAR a = CALCULATE ( [Percent Complete by Task Status], '13/05/2020 - all tasks'[Task Status] = "Open" ) * 0 VAR b = CALCULATE ( [Percent Complete by Task Status], '13/05/2020 - all tasks'[Task Status] = "In progress" ) * 0.5 VAR c = CALCULATE ( [Percent Complete by Task Status], '13/05/2020 - all tasks'[Task Status] = "In Review" ) * 0.75 VAR d = CALCULATE ( [Percent Complete by Task Status], '13/05/2020 - all tasks'[Task Status] = "Complete" ) * 1 RETURN IF ( HASONEVALUE ( '13/05/2020 - all tasks'[Task Status] ), SWITCH ( SELECTEDVALUE ( '13/05/2020 - all tasks'[Task Status] ), "Open", a, "In progress", b, "In Review", c, "Complete", d ), a + b + c + d )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community SupportHi Anonymous ,
Try this:
Measure =
VAR a =
CALCULATE (
[Percent Complete by Task Status],
'13/05/2020 - all tasks'[Task Status] = "Open"
) * 0
VAR b =
CALCULATE (
[Percent Complete by Task Status],
'13/05/2020 - all tasks'[Task Status] = "In progress"
) * 0.5
VAR c =
CALCULATE (
[Percent Complete by Task Status],
'13/05/2020 - all tasks'[Task Status] = "In Review"
) * 0.75
VAR d =
CALCULATE (
[Percent Complete by Task Status],
'13/05/2020 - all tasks'[Task Status] = "Complete"
) * 1
RETURN
IF (
HASONEVALUE ( '13/05/2020 - all tasks'[Task Status] ),
SWITCH (
SELECTEDVALUE ( '13/05/2020 - all tasks'[Task Status] ),
"Open", a,
"In progress", b,
"In Review", c,
"Complete", d
),
a + b + c + d
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey
This solution works!! Thank you so much for your help, I appreciate it greatly!
For my own education, do you mind taking a minute to run me through how the DAX works in your version of the measure?
Specifically why
a) in the Variables, why did my measure not produce the right result when I used FILTER but yours does? The syntax for CALCULATE expects a filter in the 2nd argument
b) Can you run me through what is happening in the IF / HASONEVALUE / SWITCH / SELECTEDVALUE section? From what I understand, the syntax is saying that IF there is one value task status (which would be the sub lines of the matrix), switch the selected value with the variables. The subtotals (ie the time phase) contain multiple values of 'Task Status' therefore returning false, which is the sum of the variables.
thank you once again
GC4002
- Icey
Hi Anonymous ,
Glad to help you. For you questions,
a) in the Variables, why did my measure not produce the right result when I used FILTER but yours does? The syntax for CALCULATE expects a filter in the 2nd argument
Your expression's logic is to calculate "a+b+c+d" for each task status and the subtotals.
In my expression of "'13/05/2020 - all tasks'[Task Status] = "Open"", the part after "=" doesn't contain a function, such as MAX(...). It is just a value. So, it works the same as "Filter('13/05/2020 - all tasks','13/05/2020 - all tasks'[Task Status] = "Open")".
b) Can you run me through what is happening in the IF / HASONEVALUE / SWITCH / SELECTEDVALUE section? From what I understand, the syntax is saying that IF there is one value task status (which would be the sub lines of the matrix), switch the selected value with the variables. The subtotals (ie the time phase) contain multiple values of 'Task Status' therefore returning false, which is the sum of the variables.
Please check the comment in the expressions.
HASONEVALUE ( '13/05/2020 - all tasks'[Task Status] ), ---------This is used to change the subtotal/total value. If there is only one value (only one task status), return "SWITCH(...)", else "a+b+c+d".SWITCH ( SELECTEDVALUE ( '13/05/2020 - all tasks'[Task Status] ), ------This is a judgment condition. If you have multiple judgment conditions, you could replace it with TRUE(), and write your conditions below. "Open", a, --------If SELECTEDVALUE ( '13/05/2020 - all tasks'[Task Status] ) = "Open", return a, and the others continue to judge afterward. "In progress", b, "In Review", c, "Complete", d ) ---------------Records that do not meet the above conditions will return a null value (Blank()).Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.