% of categorized data when not all data has a category that is not % Grand Total of count of categor

[Anonymous]

Hello,

Usual apologies for this question if it's easy. I am a relatively new user who uses Power BI desktop infrequently, sadly.

I think this question will have an easy answer, for those good with measures and filter contexts, or maybe it's even easier than that.

I've been fighting it for a couple of hours, I think my problem is I don't fully appreciate the filter context (I haven't completed enough SQBI.com courses, those guys are the best!) and I'm not properly grasping how to tell Power BI what I want to calculate in a DAX measure.  I know enough to know the difference between M query language data extraction and initial processing, and I've done work with a number of measures but I'm still by no means proficient with measures.

Here is my problem in simplified form:

If I have data like this:

Record	Category
1	Apples
2	Apples
3	Pears
4	Bolts
5
6	Apples
7

We can see that Apples occurs 3 out of 7 times or about 42% of the time.

Of the data with categories, Apples occurs 3 out of 5 times or about 60% of the time.

Now let's say the table has 1000's of records where not all have categories, and the # of categories is say 50 different categories.

When using a filter or a slicer I want to show the % of data in a given category out of all data that has categories.

Or put another way I don't want the effect of the 'Show as % of grand total' where as soon as you slice the data by any number of categories the %'s change to be %'s that sum to 100%

So imagine there's 10,000 records.  8000 have categories.  5000 are categorized as Apples, and 1000 are categorized as Pears.  So the % of categorized data that is apples is 62.5%, and for pears it is 12.5%.  How do I show that in a matrix or table and -ONLY- show the category Apples (or any combination of categories the user would like to select) like this:

Category	% of categorized data
Apples	62.5%
Pears	12.5%

Does that make sense as a problem?

Thanks again to this wonderful community for taking the time to read my question and for any who provide some possible solutions.

- Jim

[danextian]

hi @Anonymous ,

Please try this:

% of Category =
DIVIDE (
    SUM ( 'Table'[Record] ),
    CALCULATE ( SUM ( 'Table'[Record] ), ALL ( 'Table'[Category] ) )
)

You should be getting the result below

[Anonymous]

Hello danextian,

When I create a test PBIX file with the data I described:

And then in the desktop create this measure:

When I try to place that measure in a visual it's not working, I think because we're trying to SUM over text data not numbers?

Again thanks so much for looking at my problem and assisting me.

Very large apologies if there was an implicit step I've missed. I have not had my head in Power BI for a few months.

- Jim

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Here I create a sample to have a test.

Measure:

Measure = 
VAR _COUNT_EACH_CATEGORY = CALCULATE(COUNT('Table'[Category]),ALLEXCEPT('Table','Table'[Category]))
VAR _TOTAL = CALCULATE(COUNT('Table'[Category]),FILTER( ALL('Table'),'Table'[Category]<>BLANK()))
RETURN
DIVIDE(_COUNT_EACH_CATEGORY,_TOTAL)

Remove Blank in [Category] column on page level/visual level filter. Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous]

Brilliant!  That works perfectly!