Finding Percent of Total within Table

[jwesle]

Hello, I am having difficulty determining how to find a % of total values based on multiple criteria within one table. The data table contains a Cust ID column, a Location Column, and an Item ID column. It's difficult to put into words, but I am trying to determine the percentage of items that are within each Cust ID and Location pairing (each Cust ID would add up to 100%). Example: For Cust ID 20001, there are six total items. One in Location 12 (17%), 2 in Location 14 (33%), and three in Location 20 (50%).

Raw Data:

Cust ID	Location	Item ID
20001	12	1
20001	14	2
20001	14	3
20001	20	4
20001	20	5
20001	20	6
35000	12	7
35000	12	8
35000	14	9
35000	14	10
35000	14	11

Desired Output:

Cust ID	Location	% of ID/Loc
20001	12	17%
20001	14	33%
20001	20	50%
35000	12	40%
35000	14	60%

Is there any way to create this view?

Thanks!

[AlB · Accepted solution]

Hi @jwesle

Try this, where Table1 is the name of the table shown

1. Set Table1[Cust ID] and Table1[Location] in the rows of a matrix visual 

2. Set this measure in values of the matrix visual:

Percentage of ID/Loc =
DIVIDE (
    COUNT ( Table1[Item ID] ),
    CALCULATE ( COUNT ( Table1[Item ID] ), ALL ( Table1[Location] ) )
)

[jwesle]

Thank you. I was able to get it to work using the code below.

Much appreciated.