Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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's avatar
    v-lid-msft
    6 years ago

    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's avatar
    v-lid-msft
    6 years ago

    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.

6 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-lid-msft
        Community Support
        6 years ago

        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.