cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adris-misra
Frequent Visitor

Pie chart dynamic legend

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.

WorkItemSprintStatus
AS1WIP
AS2WIP
AS3WIP
AS4Done
BS1WIP
BS2WIP
BS3Done
CS1WIP
CS2Done
DS1Done
ES2Done
FS2WIP
FS3Done
GS2WIP
GS3WIP
GS4Done

 

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.

3 ACCEPTED SOLUTIONS
v-lid-msft
Community Support
Community Support

Hi @adris-misra ,

 

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

 

24.PNG

 

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
    )

 

26.PNG27.PNG

 


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.

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.

View solution in original post

Hi @adris-misra ,

 

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 () )

2.PNG3.PNG

 


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.

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.

View solution in original post

Hi @adris-misra ,

 

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 () )
    )

 

3.PNG


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.

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.

View solution in original post

6 REPLIES 6
v-lid-msft
Community Support
Community Support

Hi @adris-misra ,

 

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

 

24.PNG

 

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
    )

 

26.PNG27.PNG

 


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.

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.

View solution in original post

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.

Hi @adris-misra ,

 

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 () )

2.PNG3.PNG

 


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.

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.

View solution in original post

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.

Hi @adris-misra ,

 

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 () )
    )

 

3.PNG


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.

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.

View solution in original post

It worked great, thank you

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors