Seeking Assistance with Calculations and Percentages on a Table

[0Experience]

Hello Everyone,

I hope you're doing well. I have a slide with a filter and a table displaying Year, Zone, and Loc_Pop. When I select an option from the filter, the table updates to show Year, Zone, and Loc_Pop for the selected location. Here, I am considering "Cat_total" from the category.

Now, I'm looking to enhance the table by adding some additional columns:

1. Sum_of_the_Same_Zone_Loc_Pop:

   • This column should display the sum of Loc_Pop for all locations within the same zone as the selected location from the filter (for each year.)
   • Based on the above example, I want to sum for all location those are on the "N" zone (for each year).

2. Percentage_Zone:

   • This column should show the percentage of the "Sum_of_the_Same_Zone_Loc_Pop" compared to the "Loc_Pop".

3. Sum_of_All_Zone_Loc_Pop:

   • This column should show the sum of Loc_Pop for all zones for each year.

4. Percentage_All_Zones:

   • Similar to the previous column, this should display the percentage of "Sum_of_All_Zone_Loc_Pop" compared to the individual "Loc_Pop".

The output should something like the below:

I'd greatly appreciate any guidance on how to implement these additions effectively. Thank you so much for your help!

.pibx can be accessed from here (Google Drive Link). 

[sayaliredij · Accepted solution]

Hi @0Experience 

Please refer the file saved in following location

filter_sum.pbix

Measures:

1. Sum of the Same Zone Loc Pop:

   This measure calculates the sum of the population in the same zone as the current location.

   Sum_of_the_Same_Zone_Loc_Pop = CALCULATE(SUM(Table1[Loc_Pop]), ALL(Table1[Location]))

2. Percentage Zone:

   This measure calculates the percentage of the total population that is in the same zone as the current location.

   Percentage_Zone = FORMAT(DIVIDE(SUM(Table1[Loc_Pop]), [Sum_of_the_Same_Zone_Loc_Pop]), "0.00%")

3. Sum of All Zone Loc Pop:

   This measure calculates the sum of the population in all zones.

   Sum_of_All_Zone_Loc_Pop = CALCULATE(SUM(Table1[Loc_Pop]), ALL(Table1[Location]))

4. Percentage All Zones:

   This measure calculates the percentage of the total population that is in all zones.

   Percentage_All_Zones = FORMAT(DIVIDE(SUM(Table1[Loc_Pop]), [Sum_of_All_Zone_Loc_Pop]), "0.00%")

I hope this is helpful!

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[0Experience]

@sayaliredij thanks for your answer, and sorry for the late reply.

I can see that "Sum_of_the_Same_Zone_Loc_Pop" and "Sum_of_All_Zone_Loc_Pop" have the same formula (as stated in your post, but the correct answer is in the attachment), as well as for the percentage calculation.