Matrix Visualization % of Column Total for Non-aggregated field

[vrajendran]

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. 

[v-huizhn-msft]

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]))

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

[vrajendran]

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.

[v-huizhn-msft]

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