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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vrajendran
New Member

Matrix Visualization % of Column Total for Non-aggregated field

Hi, 

 

I am using a matrix visualization in PowerBI Desktop v2.38 for a dataset, where I have 'monthly credit hours' as rows (in the format 0-10, 10-20, 20-30, and so on). And I have column headers with rank (such as VP, Manager, Officer, Analyst, etc.). I have Count of Name that goes in as values. I would basically like to have the Count of Name as a percentage figure of the column total for each rank. Currently, I only get the option of % of grand total, which shows a percentage for the grand total of all ranks. 

If not possible directly, is there a way by writing a DAX expression to calculate the Count of Name %? Any help would be appreciated. Thank you. 

 

 

 

3 REPLIES 3
v-huizhn-msft
Employee
Employee

Hi @vrajendran,

You can use ALLEXCEPT function to filter name. For instance, in the following screenshot, I add the ALLEXCEPT, it will calculate count in each name. 

count = CALCULATE(COUNTA(Test[name]),ALLEXCEPT(Test,Test[name]))

 

1.PNG

Based on your description, I am unable to reproduce your scenario, please post sample data for further analysis if ALLEXCEPT doesn't resolve your issue.

Best Regards,
Angelia

Thank you for your reply Angelia, but that's not quite what I'm looking for. I'm not looking to get a formula for count. That is done automatically by PowerBI when I bring Name field as Values. What I'm looking is to get the % instead. I've built a sample in excel and this pivot table is what I'm trying to replicate. The highlighted values in yellow which displays the % of the column total is what I'm aiming for. Hope this clarifies.

 

Sample capture.PNG

 

Hi @vrajendran,

You can create a measure using the following formula.

Percent= CALCULATE(COUNTA(Test[name]),ALLEXCEPT(Test,Test[name]))/ CALCULATE(COUNTA(Test[name]),ALL(Test)

Then create a matirx, select the group as row level, the name as column level, measure as value level, you will get expected result.

Best Regards,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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