Divide one column by another in a matrix

cwoy2j

In a Matrix, the columns are generated based on column group data fields which is dynamic. We can't directly have one column divide by another column.

In your scenario, you need to limit the scope for current row context in your calculations. You can create two measures, one for Unused, the other for Used. Then calculate the percentage based on the those two measures.

Unused Total= CALCULATE(SUM(Table[Value]),FILTER(Table,Table[Usage Status]="Unused"))

Used Total= CALCULATE(SUM(Table[Value]),FILTER(Table,Table[Usage Status]="Used"))

Used Pct= [Used Total]/([Unused Total]+[Used Total])

Unused Pct= [Unused Total]/([Unused Total]+[Used Total])

Regards,

Simon Hou

Add index column to your data model and then user that column for count.

step 1: click edit query

step 2 : add index column

and this is how it will show in your data model, you can change the header label whatever you want 

and change all formulas from sum to count like this:

Unused Total= CALCULATE(COUNT(Table[Index]),FILTER(Table,Table[Usage Status]="Unused"))