Total Measures issue - within a Matrix

[Anonymous]

Hi Everyone,

I understand the total measures issue is commonplace. I have done some research but can't identify a solution to this particular issue, so I hope someone can help out.

My report involves displaying task status within time phases, for various sub teams. I previously created a measure that gives the percentage of each task status (Open, In Review, In Progress, Complete) against the total count of tasks. 

I am required to present a summary by time phase, using a weighted value for each task status, ie

Open = 0%

In Progress = 50%

In Review = 75%

Complete = 100%

I managed this by using variables and IF(SELECTEDVALUE(status)="Complete",[% Complete by Status]*1 et cetera.

However my issue is that these task status roll in to time phases. When I try to display in a matrix, it does not sum the the weighted percentages. 

I have attached two pictures - one is of my measure, the other is the result I have. 

I would greatly appreciate any help offered!

[Icey · Accepted solution]

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.

[Anonymous]

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.

[amitchandak]

@Anonymous , make all if as filters and the try.

In case measure Grand totals(GT) are recalculated and GT might miss the row context. So use the filter in place of if and try.

That filter the data and that will not become part of grand total

[Anonymous]

Hi @amitchandak 

I don't understand your response, can you please clarify how the measure should be re-written? I have made an attempt per the screenshots, while the totals now add correctly the measure isn't doing what I expected any more.