Trouble with Percent of Overall Total

[jwessel]

Hi, I am attempting to get a percent of an overall total but cannot seem to come up with the right formula for doing so.

Here is my report.  The Total Case Count visual at the bottom is correct and that is based on a measure which is just a distinctcount of the number of Case Numbers, showing 14.6K overall for the selected Date Range in the slicer.  The visual in the middle is intended to show the number of cases worked by those individuals and then I would like the % of all Cases to be the count for the individual against the 14.6K total cases.

the Count of Case # in the VVC Case Count visual is a distinctcount of case numbers in the table and I filter for each of those caseworkers from a list of caseworkers in the table.  For example, on the Sarah Miller row, I want to see about 5.2% (760/14600*100).

I'm really stumped here as a new PowerBI user and sincerely appreciate any guidance/solution.

[viviank]

Hi jwessel,

I got the link from gdrive, and I got your report working, but it's not ideal. Here is where you can see my updates:

https://1drv.ms/u/s!Asnj1wbkvlaggfRiPfxLOoeWmZkfnw?e=56hcGY

This is what I did.

First, I saw there was a Date filter and a Conference filter on the Card visual for the Non-Pantry Outreach Total Case Count. This made the Total there wrong as it was filtering things out. So I removed those filters.

Then I noticed that the Date at the top is coming from the CMS table, and it should be referencing the Date table, so I changed that.

Then I got the percentage to work but using

REMOVEFILTERS (CMS[Caseworker]),CMS[Type of Assistance] <> "Pantry"

as follows:

Cases as Percent of Total =
DIVIDE (
DISTINCTCOUNT (CMS[Case #] ),
CALCULATE (
distinctCOUNT ( CMS[Case #]),
REMOVEFILTERS (CMS[Caseworker]),CMS[Type of Assistance] <> "Pantry"
)
)

I also set the format to Percentage for the Measure.

This is the part that confuses me. I'm not sure why I need to call out [Type of Assistance]<>"Pantry" in CALCULATE, It shouldn't be necessary as you are already filtering for this in the slicer on the right, but it was the only way I could figure out to get it to work.

I think the issue has to do with the Type of Assistance filter pointing to the same table (CMS). I bet it would work if you created a separate table for Type of Assistance and linked it to CMS and used the separate table for the slicer. But - I didn't have time to try this out today.

[jwessel]

Thanks much!  I'll review in more detail and see if I can get the changes in appropriately over the next few days.

[viviank]

I just made the update - I was right - creating another table fixes the filter.

See the link above for the updated file.

I made a new table for Type of Assistance, linked it to CMS, and used the new table for the slicer filter. Now we don't need the MS[Type of Assistance] <> "Pantry" logic anymore. You can choose whatever Types of Assistance you want on the right, and the percent of total will match those categories.

Enjoy!! 🙂

[viviank · Accepted solution]

https://1drv.ms/u/s!Asnj1wbkvlaggfRiPfxLOoeWmZkfnw?e=56hcGY

[jwessel]

Works Great!  I did change a couple of things though.  I changed the date slicer to use the date from the date table instead of the CMS.Date assistance was provided column.  I kept the filter for Conference since the data of interest was specific to their work in a single conference.  It appears that I was able to get correct and valid results without adding the type of assistance table.  Would you like me to re-post the file on my GDrive again?

[viviank]

No need to post, I am good. Glad it worked for you!

[jwessel]

ok, here is link to the pbix file since I cannot see any upload button.  https://svdpcincinnati.sharepoint.com/:u:/s/operations/ER132B4VYXRFsWz5CqL0VUUBpZxucb1S5o4gQU2NXMHTc...

If you cannot access, please let me know.

[viviank]

Hi jwessel, Sorry I can't access this. Not sure if it's an issue on my end or your end.

[jwessel]

There is this one on my GDrive as well.  Can you get to this one?

https://drive.google.com/file/d/1scUgxIet_QGLPF53kNBZFGEbf8QfdnvT/view?usp=sharing

[mahenkj2]

Hi @jwessel ,

Please post a sample data set with expected slicers. Better if you can upload a pbi file, or at least data in the format we can right away copy and test, with desired output snapshot.

[jwessel]

Here is another link from GDrive if the other does not work.

https://drive.google.com/file/d/1scUgxIet_QGLPF53kNBZFGEbf8QfdnvT/view?usp=sharing

[viviank]

I can see this one 🙂

[jwessel]

thanks for that input viviank.  Maybe I should have mentioned that the caseworkers on that first visual are just a subset of a larger group of caseworkers and the 14.6K number is a total of the cases worked for all caseworkers. That being said, the concept is to filter/slice for any subset of caseworkers to see their total cases worked and how that compares to the full complement of cases for all the caseworkers in the set.

[viviank]

Thanks for the clarification.

I would try a measure, something like this:

Cases as Percent of Total =
DIVIDE (
DISTINCTCOUNT ( 'Cases'[Case Id] ),
CALCULATE (
DISTINCTCOUNT ( 'Cases'[Case Id]),
ALL ( 'Cases'[Case Id])
)
)

Here you are doing a division. The numerator is the number of cases, which is what you see on each row. When you look at Sarah Miller, it's 760. The DAX filters it by the row.
You want a percentage, so you want to divide by the total number of cases. Because Power BI is filtering the row to just show Sarah Miller, you need to override the filter to count all the cases. You can do this with ALL as shown above, or you can also use REMOVEFILTERS if you want to remove some filters but keep others.

If you want to make this easier to follow in your PBIX, you can separate it into several measures, like this:

Cases for One Person =
DISTINCTCOUNT ( 'Cases'[Case Id] )

Total Cases = 
CALCULATE (
DISTINCTCOUNT ( 'Cases'[Case Id]),
ALL ( 'Cases'[Case Id])
)

Then:

Cases as Percent of Total =
DIVIDE (
[Cases for One Person],
[Total Cases]
)

[jwessel]

the following just gives me 100% on each row.

Cases as Percent of Total =

DIVIDE (

DISTINCTCOUNT (CMS[Case #] ),

CALCULATE (

DISTINCTCOUNT ( CMS[Case #]),

ALL ( CMS[Case #])

)

)

[viviank]

I believe we're close, sometimes it takes a bit to get the filtering right.

Let's try using ALL() without specifying any fields.

Cases as Percent of Total =

DIVIDE (

DISTINCTCOUNT (CMS[Case #] ),

CALCULATE (

DISTINCTCOUNT ( CMS[Case #]),

ALL ()

)

)

[jwessel]

tried the following per your suggestion

Cases as Percent of Total =

DIVIDE (

DISTINCTCOUNT (CMS[Case #] ),

CALCULATE (

distinctCOUNT ( CMS[Case #]),

ALL()

)

)

Result is 

[viviank]

At least we're seeing a percentage --- 0.01 is 1%, 0.02 is 2% and so on -- but it's not the right percentage.

I'm wondering if something is going on with the card visual for Total Case Count. Can you show what the formula is for that? And are there any filters on the Card visual, or the page?

[viviank]

I see Non-Pantry in the title, which makes me think maybe the total you are showing is not the total of everything.

[jwessel]

Thanks!  I will give that a try and see what happens.