Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AshishJuneja
Frequent Visitor

Parent and child on the same axis in bar chart

Hi,

 

I have a customer requirement to show the parent bar along with it's child bars in a bar chart.

 

For example, If we have the following hierachy:  Global --> Region --> Country --> State --> District.

 

The top level bar graph will contain one bar for Global and one bar for each Region.

Second level will contain one bar for the Region which is drilled down and one bar for each Country.

Third level will contain one bar for the Country which is drilled down and one bar for each State.

 

Hope the question is clear. 

I would really appreciate some help here. 

 

Thanks.

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @AshishJuneja ,

 

Try this:

drill down.gif

Reogin Country = 
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[Region] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Region] ),
                FILTER ( 'Table', 'Table'[Region] <= EARLIER ( 'Table'[Region] ) )
            )
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Country] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Country] ),
                FILTER ( 'Table', 'Table'[Country] <= EARLIER ( 'Table'[Country] ) )
            )
                + DISTINCTCOUNT ( 'Table'[Region] )
    )
)
Country State = 
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[Country] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Country] ),
                FILTER ( 'Table', 'Table'[Country] <= EARLIER ( 'Table'[Country] ) )
            )
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[State] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[State] ),
                FILTER ( 'Table', 'Table'[State] <= EARLIER ( 'Table'[State] ) )
            )
                + DISTINCTCOUNT ( 'Table'[Country] )
    )
)
State District = 
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[State] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[State] ),
                FILTER ( 'Table', 'Table'[State] <= EARLIER ( 'Table'[State] ) )
            )
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[District] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[District] ),
                FILTER ( 'Table', 'Table'[District] <= EARLIER ( 'Table'[District] ) )
            )
                + DISTINCTCOUNT ( 'Table'[State] )
    )
)
Modified Measure = 
SWITCH (
    TRUE (),
    ISFILTERED ( 'State District'[State] ),
        IF (
            MAX ( 'State District'[State] ) IN VALUES ( 'Table'[District] ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[District] = MAX ( 'State District'[State] )
                        && 'Table'[Country] = MAX ( 'Reogin Country'[Region] )
                        && 'Table'[State] = MAX ( 'Country State'[Country] )
                )
            ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[State] = MAX ( 'State District'[State] )
                        && 'Table'[Country] = MAX ( 'Reogin Country'[Region] )
                        && 'Table'[State] = MAX ( 'Country State'[Country] )
                )
            )
        ),
    ISFILTERED ( 'Country State'[Country] ),
        IF (
            MAX ( 'Country State'[Country] ) IN VALUES ( 'Table'[State] ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[State] = MAX ( 'Country State'[Country] )
                        && 'Table'[Country] = MAX ( 'Reogin Country'[Region] )
                )
            ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[Country] = MAX ( 'Country State'[Country] )
                        && 'Table'[Country] = MAX ( 'Reogin Country'[Region] )
                )
            )
        ),
    ISFILTERED ( 'Reogin Country'[Region] ),
        IF (
            MAX ( 'Reogin Country'[Region] ) IN VALUES ( 'Table'[Country] ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER ( 'Table', 'Table'[Country] = MAX ( 'Reogin Country'[Region] ) )
            ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER ( 'Table', 'Table'[Region] = MAX ( 'Reogin Country'[Region] ) )
            )
        ),
    SUM ( 'Table'[Measure] )
)

 

For more details, please check the attached .pbix file.

 

 

Best Regards,

Icey

 

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
Icey
Community Support
Community Support

Hi @AshishJuneja ,

 

Try this:

drill down.gif

Reogin Country = 
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[Region] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Region] ),
                FILTER ( 'Table', 'Table'[Region] <= EARLIER ( 'Table'[Region] ) )
            )
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[Country] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Country] ),
                FILTER ( 'Table', 'Table'[Country] <= EARLIER ( 'Table'[Country] ) )
            )
                + DISTINCTCOUNT ( 'Table'[Region] )
    )
)
Country State = 
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[Country] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Country] ),
                FILTER ( 'Table', 'Table'[Country] <= EARLIER ( 'Table'[Country] ) )
            )
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[State] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[State] ),
                FILTER ( 'Table', 'Table'[State] <= EARLIER ( 'Table'[State] ) )
            )
                + DISTINCTCOUNT ( 'Table'[Country] )
    )
)
State District = 
UNION (
    ADDCOLUMNS (
        VALUES ( 'Table'[State] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[State] ),
                FILTER ( 'Table', 'Table'[State] <= EARLIER ( 'Table'[State] ) )
            )
    ),
    ADDCOLUMNS (
        VALUES ( 'Table'[District] ),
        "Order",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[District] ),
                FILTER ( 'Table', 'Table'[District] <= EARLIER ( 'Table'[District] ) )
            )
                + DISTINCTCOUNT ( 'Table'[State] )
    )
)
Modified Measure = 
SWITCH (
    TRUE (),
    ISFILTERED ( 'State District'[State] ),
        IF (
            MAX ( 'State District'[State] ) IN VALUES ( 'Table'[District] ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[District] = MAX ( 'State District'[State] )
                        && 'Table'[Country] = MAX ( 'Reogin Country'[Region] )
                        && 'Table'[State] = MAX ( 'Country State'[Country] )
                )
            ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[State] = MAX ( 'State District'[State] )
                        && 'Table'[Country] = MAX ( 'Reogin Country'[Region] )
                        && 'Table'[State] = MAX ( 'Country State'[Country] )
                )
            )
        ),
    ISFILTERED ( 'Country State'[Country] ),
        IF (
            MAX ( 'Country State'[Country] ) IN VALUES ( 'Table'[State] ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[State] = MAX ( 'Country State'[Country] )
                        && 'Table'[Country] = MAX ( 'Reogin Country'[Region] )
                )
            ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[Country] = MAX ( 'Country State'[Country] )
                        && 'Table'[Country] = MAX ( 'Reogin Country'[Region] )
                )
            )
        ),
    ISFILTERED ( 'Reogin Country'[Region] ),
        IF (
            MAX ( 'Reogin Country'[Region] ) IN VALUES ( 'Table'[Country] ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER ( 'Table', 'Table'[Country] = MAX ( 'Reogin Country'[Region] ) )
            ),
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER ( 'Table', 'Table'[Region] = MAX ( 'Reogin Country'[Region] ) )
            )
        ),
    SUM ( 'Table'[Measure] )
)

 

For more details, please check the attached .pbix file.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AshishJuneja
Frequent Visitor

Thanks @TheoC for the quick response. You can assume the data to be like as given below. I have tried reformatting the data to achieve the same but it didn't work out. To provide further details, I tried create a row for total for each parent at the child row, so that i can get it at the same axis, but with multiple hierarchy and drill down required, it is not working as expected. If you have any other ideas that will be very helpful. 

 

RegionCountryStateDistrictMeasure
RPWA100
RPWB200
RPXC300
RPXD400
RQYE500
RQYF600
RQZG700
RQZH800

 

Requirement: Initially, Show a bar for Region and two bar for Countries(P and Q).

When drilled down on P, the chart will show one bar for P and two bars for it;s two children(W and X) and so on.

Thanks.

Hi @AshishJuneja 

 

Beautiful! You can just layer the columns under the X Axis like the below screenshot. From here, you can then just use the drilldown buttons on the visual to get to next layer 🙂

 

TheoC_0-1652081165750.png

TheoC_1-1652081269478.png

 

 

I have also attached PBIX to further assist.

 

Hope this helps 🙂

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Hi @TheoC,

With the given solution I can see all the sub categories but that is not what I intend to achieve. What I need is: 

Initially, Show a bar for Region and two bar for Countries(P and Q).

When drilled down on P, the chart will show one bar for P and two bars for it's two children(W and X) and so on.

 

Thanks.

 

 

Hi @AshishJuneja 

 

Set it up like the below image and then use the Single Drill Down button (updated PBIX attached).

 

TheoC_0-1652082061397.png

TheoC_1-1652082111824.png

 

Hope this helps 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

Hi @AshishJuneja 

 

Depending on how the your data is structured, you should be able to layer the hierarchy in order on the X-Axis field in the Visualisation pane and then use the drilldown feature on the chart itself to go down to the next level(s).

 

If the above doesn't assist, can you provide an example of your data.

 

Thank you 🙂

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.