Need to calculate % of Total with some catches

[zcaruso]

Hello,

I don't believe this is too complicated but i'm still new to Power BI so I can't figure it out. What I need is a measure to calculate % of true Total on a Top 10. Apparently I'm not set up to easily do what I want. 

So I have 2 tables, an agent lookup table and a master table with all cases. I've joined the tables in my model to only show me cases that were created by one of our agents. I have a COUNTROWS measure to give me the total CaseCount. If i show that measure of a % of the total it looks good, then after i filter to the top 10 categories it's giving me the % of the top 10 not the overall total. I know why, I just don't know how to fix it. I tried using the ALL function on the cases table but the calculation doesn't consider the join, it just counts the whole table, closer to 12k, not 7k after the join. 

I went manual for now as = CaseCount/7943 to give me what i need but would like to have a more automated measure, if possible.

Hopes that's enough to start. Thanks!

Zach

[dax]

Hi @zcaruso , 

If possible, could you please tell us if your question has been resolved. If so, in order to close the thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

If not, could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Best Regards,
Zoe Zhi

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

[Ashish_Mathur]

Hi,

Does this measure work

=COUNTROWS(Data)/CALCULATE(COUNTROWS(Data),ALL(Data1[Categries])

Data1 is the master table with the Categories column.  There should be a relationship from the Data Table to the Data1 Table.

If this does not help, then share the link from where i can download your PBI file.

[edhans · Accepted solution]

See the attached file.

I created three measures to make it very clear, but you can consolidate this into fewer measures if you want.

To get a simple count of cases per agent:

Case Count = COUNTROWS('Cases')

 To get the total cases irrespective of any filters on the report/model:

Case Total = COUNTROWS(ALL('Cases'))

To get the percent of total for your top 10 agents.

Percent of Case Total = DIVIDE('Cases'[Case Count],'Cases'[Case Total])

Hope that helps. If not, please read this to help us get you a better answser.