Showing counts of different status (exists on different columns) as a hierarchical visual

[Anonymous]

Hi!

I am trying to show the counts of different statuys of the IDs in a hierarchical tree visual, but I am facing issues on the counts just adding up . Basically the data looks something like below:

ID	Status1	Status2	Status3	Status4	Status5	Status6
1	Y	Y	Y
2	Y
3	Y	Y	Y	Y
4	Y	Y	Y	Y	Y	Y
5	Y	Y
6	Y	Y	Y	Y
7	Y	Y	Y
8	Y	Y	Y	Y	Y	Y

What I want to show is  a tree with total unique count of IDs, dropped own to different status counts and that to be drilled down to each IDs.

If someone has done this and has ideas around it would be really appreciated.

Thanks

[Greg_Deckler · Accepted solution]

So, generally you would unpivot all of those Status columns in Power Query and then things usually become trivial.

If that is not the case, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[v-gizhi-msft · Accepted solution]

Hi,

Please take the following steps:

1)Unpivot all status columns.

2)Create a table by Enter data as below:

3)Try this measure:

Count_of_each_id = 
IF (
    ISINSCOPE ( 'Level'[Lev8] ),
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[ID] = 8 && 'Table'[Value] = "Y" )
    ),
    IF (
        ISINSCOPE ( 'Level'[Lev7] ),
        CALCULATE (
            COUNT ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[ID] = 7 && 'Table'[Value] = "Y" )
        ),
        IF (
            ISINSCOPE ( 'Level'[Lev6] ),
            CALCULATE (
                COUNT ( 'Table'[Value] ),
                FILTER ( 'Table', 'Table'[ID] = 6 && 'Table'[Value] = "Y" )
            ),
            IF (
                ISINSCOPE ( 'Level'[Lev5] ),
                CALCULATE (
                    COUNT ( 'Table'[Value] ),
                    FILTER ( 'Table', 'Table'[ID] = 5 && 'Table'[Value] = "Y" )
                ),
                IF (
                    ISINSCOPE ( 'Level'[Lev4] ),
                    CALCULATE (
                        COUNT ( 'Table'[Value] ),
                        FILTER ( 'Table', 'Table'[ID] = 4 && 'Table'[Value] = "Y" )
                    ),
                    IF (
                        ISINSCOPE ( 'Level'[Lev3] ),
                        CALCULATE (
                            COUNT ( 'Table'[Value] ),
                            FILTER ( 'Table', 'Table'[ID] = 3 && 'Table'[Value] = "Y" )
                        ),
                        IF (
                            ISINSCOPE ( 'Level'[Lev2] ),
                            CALCULATE (
                                COUNT ( 'Table'[Value] ),
                                FILTER ( 'Table', 'Table'[ID] = 2 && 'Table'[Value] = "Y" )
                            ),
                            IF (
                                ISINSCOPE ( 'Level'[Lev1] ),
                                CALCULATE (
                                    COUNT ( 'Table'[Value] ),
                                    FILTER ( 'Table', 'Table'[ID] = 1 && 'Table'[Value] = "Y" )
                                ),
                                CALCULATE ( COUNT ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Value] = "Y" ) )
                            )
                        )
                    )
                )
            )
        )
    )
)

4)The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[Greg_Deckler · Accepted solution]

So, generally you would unpivot all of those Status columns in Power Query and then things usually become trivial.

If that is not the case, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490