Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
brendo
Helper I
Helper I

How to measure number of records across two subcategories?

I am trying to better understand how to use measures to add details to visualizations, rather than creating new tables. 
 
I am trying to add data labels to a bar chart which show the % proportion each value occurs against the grand total for all value occurences. I am trying to create this data label using a measure.
 
my data looks like this:
example = {("Alice", "Yellow"),("Alice", "Yellow"),("Mark", "Blue"),("Mark", "Blue"), ("Mark", "Blue"), ("Mark", "Blue"), ("Alice", "Red"), ("Mark", "Yellow"), ("Alice", "Blue")}

 

I have created a cluster bar chart which has the color counts on one axis, color categories on another axis, and the bars are symbolized according to name (picture below). 

 

I would like to have data labels in each bar that show the % proportion of each bar's count against the grand total of counts across all categories. 


For example, for Alice's Blue bar, I would like the bar to have a data label of 11% (since that bar shows a count of 1, and the grand total across all category counts is 9; 1/9); Marks' Blue bar should have a label of 44% (4/9), etc.

 

I would like to achieve this using a measure, and have been playing around with different measures, but I think what I'm having trouble with is understanding how a measure stores values. 


For example, I can really easily visualize creating a summary table, which I've done using dax and power query, which would give me a simpe table of grouped values and their respective counts. But I'm having trouble visualizing how a measure can be used instead of a table to run calculations for categories and subcategories. 


I think I have to do the following: 

1. Get total count of all rows (to use as a denominator for calculating the proportions). ex: var totalcount = COUNTROWS(table)

2. Get counts across two categories -- names and colors 

3. Divide counts of values against total row count. ex: var proportions = DIVIDE(counts, totalcount)

 

I think what I'm struggling with is number 2 -- getting counts across two categories and understanding how this is done.

 

clustered_bar_chart.PNG

1 REPLY 1
lbendlin
Super User
Super User

Read about REMOVEFILTERS  - but you can do that without them too.

 

lbendlin_0-1702250646520.png

see attached.

 

 

 

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors