how to create hirerarchy visual for country wise (parent) + year wise (child) population data

[Anonymous]

hi all,

want to create a Table type visual for country+year wise population data, please help how i can do it.

sample data ( actual Excel file contains columns like: Country   2012   2013   2014   2015 ...   population figues under years columns, i have Unpivot to have data in below format ) :

Country        Year         Population

Brazil           2012              100010

Brazil           2013              100020

Brazil           2014              100030

Canada       2012               200010

Canada       2013               200020

Canada       2014               200030

desired output :

Country       Population     Population Prvs Yr

Brazil                 300060

  2012               100010

  2013               100020                       100010

  2014               100030                       100020

Canada             600060

  2012               200010

  2013               200020                       200010

  2014               200030                       200020

Total                 900120

regards

[amitchandak]

@Anonymous , best is create a separate year table and try measure like

 this year = calculate(sum(Table[Population]), filter(allselected(Year), Year[year] = max(Year[year])))
last year = calculate(sum(Table[Population]), filter(allselected(Year), Year[year] = max(Year[year])-1 ))


without date table
last year = calculate(sum(Table[Population]), filter(allselected(Table), Table[Country] = max(Table[Country]) && Table[year] = max(Table[year])-1 ))

[Anonymous]

hi @amitchandak 

first of all :-  definitely i am doing a mistake(s), please guide to avoid 🙂

( below is my first Reply to your solution and this reply is after i implemented your formulas )

Table: Population
Columns: Country Name, PopulationYr, year
DAX formula:
Pop last year = calculate(sum(Population[PopulationYr]), filter(allselected(Population), Population[Country] = max(Population[Country]) && Population[year] = max(Population[year])-1 ))

Visual = Table with all 3 columns and want to add this measure 'Pop last year' when i drag it to visual, it is showing error - big gray box saying " Can't display the visual " 

also tried this DAX formula with separate Year table, see below screenshot for this ( it is showing same figures in both columns, this year and last year : 

this year = calculate(sum(Population[PopulationYr]), filter(allselected(Years), Years[year] = max(Years[year]) ))

last year = calculate(sum(Population[PopulationYr]), filter(allselected(Years), Years[year] = max(Years[year])-1 ))

still my mentioned visual is not what I want to have it, its simple no hierarchy view.

regards

[Anonymous]

great 👍 @amitchandak  

but what about the already requested hierarchy visual, how I can achieve this, please point me to any link where i can get step by step guide for this particular visual because this will be very useful in various reports. below is screenshot for this requirement ( OR i have to create another topic for this? )

regards

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

re-uploaded files.

hi @Ashish_Mathur , sorry for inconvenience, i have modified and replaced my .pbix file and also uploaded excel file from which i loaded data and transformed to unpivot for this report. both links are below:

Link for PBIX file 

Link for Excel file 

hope this will help to sort out the problem.

3rd page for World Population year wise.

regards

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur  thanks a bunch for your help 

how i can arrange in visual to have hierarchy view as mentioned earlier (like below screenshot)... please help.

regards

[Ashish_Mathur · Accepted solution]

Hi,

Change the visual to a matrix visual.  Drag Country and Year to the Row section.

[Anonymous]

great help !!! now it works fine as required. thanks very much for your passions to guide/help us 🙂

kind regards

[Ashish_Mathur]

You are welcome.

[Anonymous]

@Ashish_Mathur 

thank you for interest to help. page 2 contains Population related working. these are not a professional files but just to understand some features and how i can work using Power BI.

Link for PIBX 

regards

EDIT: sorry, it page 3 for Population Data