cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ali_akram Frequent Visitor
Frequent Visitor

Percentage of total and page level filters

Hi everyone,

 

I am new to Power Bi.

 

I am working on an investment file in excel with these columns (Investment Name,  Geography, Industry, Client, Investment Value, Total Return (%) etc)

 

I have applied slicers on the report view for Geography, Industry, Client etc. When we select any geography it only shows the table for that region etc.

 

I need to two new calculated columns in my final table 

 

  1. Percentage Allocation: This is the investment value / total investment value and it need to change if we select different filters. I have calculated the value however it keeps calculating the allocation based on total investment value rather than total investment value of page level filters applied. I can't just show the investment value in percentage since I need to use the figure in the second calculated column as per below.
  2. Weighted Return: This is the multiple of percentage allocation and total return. Easy to do of percentage allocation is correct.

Appreciate your feedback.

 

Regards,

Ali Akram

7 REPLIES 7
Super User
Super User

Re: Percentage of total and page level filters

@ali_akram can you share your measure you created to calculate the % and probably I can suggest the change in those measures.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





ali_akram Frequent Visitor
Frequent Visitor

Re: Percentage of total and page level filters

@parry2kI have tried both calculations as per below. (These are calculated columns rather than measures).The formula should be "Investment Value"/"sum of "Investment Value". The sum should be based on the values selected in slicer (Geography, client, industry etc).

 

  1. Table_Investment[value]/sum(Table_investment[value]).  This does not work since it takes sum of all values rather than filtered ones only
  2. Table_Investment[value]/calculate(sum(table_investment[value]),allselected(Table_Investment)). This does not work also for some reason.

Regards,

Ali Akram

Super User
Super User

Re: Percentage of total and page level filters

@ali_akram you should add these as measures

 

DIVIDE( 
SUM(Table_Investment[value]),
calculate(sum(table_investment[value]),allselected(Table_Investment))
)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





ali_akram Frequent Visitor
Frequent Visitor

Re: Percentage of total and page level filters

@parry2k The formula doesn't work. It just shows 100% allocation for each investment. The first allocation column shows the correct values while the last allocation column shows 100% for each investment based on the formula you shared. 

Allocation.jpg

Super User
Super User

Re: Percentage of total and page level filters

@ali_akram just to confirm you added it as measure correct?






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





ali_akram Frequent Visitor
Frequent Visitor

Re: Percentage of total and page level filters

@parry2k No. I added it as a new column.

 

I have corrected it and used a measure now with following result. The problem is same as i was experiencing before. The formula does not consider any page level filters and slicers applied to the report. It takes allocation as percentage of all investments across all clients. I want the allocation column to change if we select other client name etc

 

Allocation.jpg

ali_akram Frequent Visitor
Frequent Visitor

Re: Percentage of total and page level filters

I am able to show the investment value as "Percentage of Grand Total" and it retains the page level filters however I can't refer the column in calculation. Is there any way i can accomplish same !

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 26 members 974 guests
Please welcome our newest community members: