How to show the consolidated bars in Power BI

[Anonymous]

Hello Expert

We have LOCATION Table(column name - LOCATION_NAME) having three values INDIA,USA & UK.

My requirement is to show Consolidated in drop down as well as X-axis in report.

Logic is:
if we select India & UK & USA that value need to pass in Consolidated and show the total of all the three.
, if we select India & USA that value need to pass in Consolidated and show the total of all the two.

Can you please suggest how to do this in Power BI.

Thanks,How to add consolidated bar

[Fowmy · Accepted solution]

@Anonymous 

Check this solution, hope it works for you. I added a new value to the Countries dimension and added a condition in the measure for the total. You may need to look at other measures where the countries dimension is shown

You can download the file: HERE

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[BA_Pete]

Hi @Anonymous ,

There is a long-winded way that involves creating separate measures for each country. How good this is as a long-term solution really depends on how many countries you have to start with, and how likely/frequently countries will be changed/added/removed from your data.

Country value measures would look like this, replacing [LOCATION_NAME] for each different country:

_indiaValue =
CALCULATE(
    SUM(table[value]),
    KEEPFILTERS(table[LOCATION_NAME] = "India")
)

Consolidated measure would be something like this:

_consolidatedValue =
CALCULATE(
    SUM(table[value]),
    ALLSELECTED(table[LOCATION_NAME])
)

*EDIT* Added KEEPFILTERS() to location measures to override CALCULATE() implicit ALL().

Also note: this technique limits formatting of visuals as each location measure must be added in the Values section, therefore you lose the axis information and may struggle to get columns to fill the visual in an appealing way.

Pete

[amitchandak]

@Anonymous , I doubt that is possible. But you can check binning and segmentation

refer

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization