Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group several days

 

For my example I have some different cases I want to group by day.

 

I don't want to create groups manually, I want that the programm finds the field with the number e.g.: "0","1","2" and group them so that i can create a percentage to see how high the percent is if i group these days.

 

Please help me asap.

 

Best regards,

Tobias 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    You can do 3options.

    Option 1:

    • Create a table with the following setup:
      ID Range Min Max
      1 0-2 Days 0 2
      2 2-5 Days 3 5
      3 +5 Days 6 99999
    • Then create the following measure:

     

    Percentage over total = 
CALCULATE (
    COUNT ( 'Cases'[Duration_to_solved [days]]] );
    FILTER (
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] );
        'Cases'[Duration_to_solved [days]]] <= SELECTEDVALUE ( Range[Max] )
            && 'Cases'[Duration_to_solved [days]]] >= SELECTEDVALUE ( Range[Min] )
    )
)
    / CALCULATE (
        COUNT ( 'Cases'[Duration_to_solved [days]]] );
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] )
    )

     

    Option 2:

    • Create the following 3 measure:
    • 0 - 2 Days = CALCULATE (
    COUNT ( 'Cases'[Duration_to_solved [days]]] );
    FILTER (
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] );
        'Cases'[Duration_to_solved [days]]] <= 2
            && 'Cases'[Duration_to_solved [days]]] >= 0
    )
)
    / CALCULATE (
        COUNT ( 'Cases'[Duration_to_solved [days]]] );
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] )
    )



2 - 5 Days = CALCULATE (
    COUNT ( 'Cases'[Duration_to_solved [days]]] );
    FILTER (
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] );
        'Cases'[Duration_to_solved [days]]]  <= 5
            && 'Cases'[Duration_to_solved [days]]] >= 3
    )
)
    / CALCULATE (
        COUNT ( 'Cases'[Duration_to_solved [days]]] );
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] )
    )

+ 5 Days = CALCULATE (
    COUNT ( 'Cases'[Duration_to_solved [days]]] );
    FILTER (
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] );
            'Cases'[Duration_to_solved [days]]] >= 6
    )
)
    / CALCULATE (
        COUNT ( 'Cases'[Duration_to_solved [days]]] );
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] )
    )

     

    Option 3:

    Create a group bin and a quick measure.

     

    Check result in PBIX file attach (with the first two options).

     

    Regards,

    MFelix

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    You can do 3options.

    Option 1:

    • Create a table with the following setup:
      ID Range Min Max
      1 0-2 Days 0 2
      2 2-5 Days 3 5
      3 +5 Days 6 99999
    • Then create the following measure:

     

    Percentage over total = 
CALCULATE (
    COUNT ( 'Cases'[Duration_to_solved [days]]] );
    FILTER (
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] );
        'Cases'[Duration_to_solved [days]]] <= SELECTEDVALUE ( Range[Max] )
            && 'Cases'[Duration_to_solved [days]]] >= SELECTEDVALUE ( Range[Min] )
    )
)
    / CALCULATE (
        COUNT ( 'Cases'[Duration_to_solved [days]]] );
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] )
    )

     

    Option 2:

    • Create the following 3 measure:
    • 0 - 2 Days = CALCULATE (
    COUNT ( 'Cases'[Duration_to_solved [days]]] );
    FILTER (
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] );
        'Cases'[Duration_to_solved [days]]] <= 2
            && 'Cases'[Duration_to_solved [days]]] >= 0
    )
)
    / CALCULATE (
        COUNT ( 'Cases'[Duration_to_solved [days]]] );
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] )
    )



2 - 5 Days = CALCULATE (
    COUNT ( 'Cases'[Duration_to_solved [days]]] );
    FILTER (
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] );
        'Cases'[Duration_to_solved [days]]]  <= 5
            && 'Cases'[Duration_to_solved [days]]] >= 3
    )
)
    / CALCULATE (
        COUNT ( 'Cases'[Duration_to_solved [days]]] );
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] )
    )

+ 5 Days = CALCULATE (
    COUNT ( 'Cases'[Duration_to_solved [days]]] );
    FILTER (
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] );
            'Cases'[Duration_to_solved [days]]] >= 6
    )
)
    / CALCULATE (
        COUNT ( 'Cases'[Duration_to_solved [days]]] );
        ALLSELECTED ( 'Cases'[Duration_to_solved [days]]] )
    )

     

    Option 3:

    Create a group bin and a quick measure.

     

    Check result in PBIX file attach (with the first two options).

     

    Regards,

    MFelix