Pie chart dynamic legend

[Anonymous]

Hi All,

I am trying to solve a problem looks like easy but not getting any such related topic that exactly match my requirement. So posting here as a new thread.

I am dealing with Sprint data. There is a Sprint Slicer with multiple selection enabled.

Sprint

S1

S2

S3

S4

Here is my base table: that has work items, sprints that are being worked on and status of the work item in that sprint.

WorkItem	Sprint	Status
A	S1	WIP
A	S2	WIP
A	S3	WIP
A	S4	Done
B	S1	WIP
B	S2	WIP
B	S3	Done
C	S1	WIP
C	S2	Done
D	S1	Done
E	S2	Done
F	S2	WIP
F	S3	Done
G	S2	WIP
G	S3	WIP
G	S4	Done

Requirement is in Pie chart I have to show old work items that are carried over from a past sprint and that has to be in two seperate categories 1. Previous sprint 2. More than 2 Sprints

Cases:

Single selction

1. If S1 is selected: There is no old works as that is start of sprint

2. If S2 is selected: A, B, C should come in Previous sprint category so in Pie chart count would be 3 in Previous sprint

3. If S3 is Selected: A, B should be in More than 2 sprint category as initiated in S1, so count will be 2 for More than 2 sprint; F, G should be in previous sprint category as initiated in S2, so count in previoyus sprint category should be 2.

Multi Selection (that makes it difficult)

1. If S2 and S3 selected:: Previous Sprint: C,F,G - Count 3; More Than 2 Sprint: A, B - Count 2

2. If S3 and S4 selected:: Previous Sprint: F - Count 1; More than 2 Sprint: A,B, G - Count 3

With this Previous Sprint and More than 2 sprint categories and corresponding counts I have to make the Pie chart that will change based on Sprint selection.

Hope I was able to make the problem statement clear, please let me know if any question, appreciate all your help.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a category table and a count measure to meet your requirement:

Then create a calculate colmun to compare the spirit,

Index = VALUE(SUBSTITUTE([Sprint],"S",""))

create the measure and put into the value field:

Count = 
VAR t =
    ADDCOLUMNS (
        CALCULATETABLE ( DISTINCT ( 'Table'[WorkItem] ) ),
        "NowSprint", CALCULATE (
            MAX ( [Index] ),
            FILTER ( 'Table', 'Table'[WorkItem] = EARLIER ( [WorkItem] ) )
        ),
        "fist", CALCULATE (
            MIN ( [Index] ),
            FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = EARLIER ( [WorkItem] ) )
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Categories'[Category] ),
        "Previous sprint", COUNTX ( t, IF ( [NowSprint] - [fist] = 1, 1, BLANK () ) ) + 0,
        "More than 2 Sprints", COUNTX ( t, IF ( [NowSprint] - [fist] >= 2, 1, BLANK () ) ) + 0
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can put the following measure into a table to meet your requirement:

IsInCategory =
VAR NowSprint =
    MAX ( 'Table'[Index] )
VAR first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = MAX ( 'Table'[WorkItem] ) )
    )
VAR category =
    IF (
        NowSprint - first = 1,
        "Previous sprint",
        IF ( NowSprint - first >= 2, "More than 2 Sprints", "Now Sprint" )
    )
RETURN
    IF ( category IN FILTERS ( Categories[Category] ), "Yes", BLANK () )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a category table and a count measure to meet your requirement:

Then create a calculate colmun to compare the spirit,

Index = VALUE(SUBSTITUTE([Sprint],"S",""))

create the measure and put into the value field:

Count = 
VAR t =
    ADDCOLUMNS (
        CALCULATETABLE ( DISTINCT ( 'Table'[WorkItem] ) ),
        "NowSprint", CALCULATE (
            MAX ( [Index] ),
            FILTER ( 'Table', 'Table'[WorkItem] = EARLIER ( [WorkItem] ) )
        ),
        "fist", CALCULATE (
            MIN ( [Index] ),
            FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = EARLIER ( [WorkItem] ) )
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Categories'[Category] ),
        "Previous sprint", COUNTX ( t, IF ( [NowSprint] - [fist] = 1, 1, BLANK () ) ) + 0,
        "More than 2 Sprints", COUNTX ( t, IF ( [NowSprint] - [fist] >= 2, 1, BLANK () ) ) + 0
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks you for the nice solution. This is working great for static pie chart reporting.

I have another table that shows item details (just the basic table data)

As there is no relationship between main table and category if I select pie section "More than 2 Sprint" or "Previous Sprint", it does not change the items based on selection. if there is some pissibility to filter the table based on Pie chart selection that will be great, if not then I will go with this.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can put the following measure into a table to meet your requirement:

IsInCategory =
VAR NowSprint =
    MAX ( 'Table'[Index] )
VAR first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = MAX ( 'Table'[WorkItem] ) )
    )
VAR category =
    IF (
        NowSprint - first = 1,
        "Previous sprint",
        IF ( NowSprint - first >= 2, "More than 2 Sprints", "Now Sprint" )
    )
RETURN
    IF ( category IN FILTERS ( Categories[Category] ), "Yes", BLANK () )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you, that works as expected.

Only a small glitch is if I do not select sny sprint so by default everything is selected, then the main table gives me record only with carryover data. D, E never appear in the table as they were never carried over. But as I have not selected the Old Work (Carry Over) Pie chart, it should show me all the data.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure to meet your requirement:

IsInCategory =
VAR NowSprint =
    MAX ( 'Table'[Index] )
VAR first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = MAX ( 'Table'[WorkItem] ) )
    )
VAR category =
    IF (
        NowSprint - first = 1,
        "Previous sprint",
        IF ( NowSprint - first >= 2, "More than 2 Sprints", "Now Sprint" )
    )
RETURN
    IF (
        COUNTROWS ( FILTERS ( Categories[Category] ) )
            = COUNTROWS ( ALL ( Categories[Category] ) ),
        category,
        IF ( category IN FILTERS ( Categories[Category] ), category, BLANK () )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It worked great, thank you