Special Parent Child Reporting Visualizer

Question

I need to reproduce exactly this mock-up in pbi, where i have a hierarchy, the possibility to hide/view only some selected levels, a measure that count Node_ID and the percentage for the total column, and aggregating the parent Node_ID "etichetta":

The starting point is a table in which i have Node_ID, Parent_Node_ID, Path, Path_length and as many columns as the hierarchy's lenght where each column contains the same reported here, and a measure that count how many times a value is counted for each Node_ID.

What i did was unpivoting the columns containing the labels in the source table, create a hierarchy, and use a filter for view/hide levels. What i wasn't able to reproduce was the aggregation for the parent's, instead i have orrible ripetition of the same Node_ID, as you can see here:

As you can see i'm far from reaching the mockup and also its simplicity.

v-yulgu-msft · Answer

Hi&nbsp;Roberto93&nbsp;,
&nbsp;
Please simplify above data model, just share some dummy data in source table and your expected result based on source data.
How to Get Your Question Answered Quickly
&nbsp;
Best regards,
Yuliana Gu

roberto93 · Answer

Data sourceLevelUnpivoted columnlevel 1level 2level 33DABD2BABD1AABD3EABE2BABE1AABE3FACF2CACF1AACFResultlevel 1count value%valuelevel 2count value%valuelevel 3count value%valueA3100%B266%D133%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;E133%&nbsp;&nbsp;&nbsp;C133%F133%possibility to remove levels and maintaining the parent child relationship&nbsp;

roberto93 · Answer

Up

v-yulgu-msft · Answer

Hi&nbsp;Roberto93&nbsp;,
&nbsp;
You could add below measures into a Matrix.
Count Total =
VAR IsLevel1 =
    ISINSCOPE ( TB2[level 1] )
VAR IsLevel2 =
    ISINSCOPE ( TB2[level 2] )
VAR IsLevel3 =
    ISINSCOPE ( TB2[level 3] )
RETURN
    IF (
        IsLevel1,
        CALCULATE (
            COUNT ( TB2[Unpivoted column] ),
            FILTER ( ALL ( TB2 ), TB2[Level] = 1 )
        ),
        IF (
            IsLevel2,
            CALCULATE (
                COUNT ( TB2[Unpivoted column] ),
                FILTER ( ALL ( TB2 ), TB2[Level] = 2 )
            ),
            IF (
                IsLevel3,
                CALCULATE (
                    COUNT ( TB2[Unpivoted column] ),
                    FILTER ( ALL ( TB2 ), TB2[Level] = 3 )
                )
            )
        )
    )

Count Level =
VAR IsLevel1 =
    ISINSCOPE ( TB2[level 1] )
VAR IsLevel2 =
    ISINSCOPE ( TB2[level 2] )
VAR IsLevel3 =
    ISINSCOPE ( TB2[level 3] )
RETURN
    IF (
        IsLevel1,
        CALCULATE (
            COUNT ( TB2[Unpivoted column] ),
            FILTER (
                ALLSELECTED ( TB2 ),
                TB2[Unpivoted column] = SELECTEDVALUE ( TB2[level 1] )
            )
        ),
        IF (
            IsLevel2,
            CALCULATE (
                COUNT ( TB2[Unpivoted column] ),
                FILTER (
                    ALLSELECTED ( TB2 ),
                    TB2[Unpivoted column] = SELECTEDVALUE ( TB2[level 2] )
                )
            ),
            CALCULATE (
                COUNT ( TB2[Unpivoted column] ),
                FILTER (
                    ALLSELECTED ( TB2 ),
                    TB2[Unpivoted column] = SELECTEDVALUE ( TB2[level 3] )
                )
            )
        )
    )

%Value = [Count Level]/[Count Total]
Click drill down button to switch between different levels.

&nbsp;
Best regards,
Yuliana Gu

Forum Discussion

Special Parent Child Reporting Visualizer

4 Replies

Related content

Special Visualization

SQL Server Mirroring - special characters

Special Character in Column Name

Special measure

Visualizing report run times across the day

Level	Unpivoted column	level 1	level 2	level 3
3	D	A	B	D
2	B	A	B	D
1	A	A	B	D
3	E	A	B	E
2	B	A	B	E
1	A	A	B	E
3	F	A	C	F
2	C	A	C	F
1	A	A	C	F

level 1	count value	%value	level 2	count value	%value	level 3	count value	%value
A	3	100%	B	2	66%	D	1	33%
						E	1	33%
			C	1	33%	F	1	33%