Divide one column by another in a matrix

[cwoy2j]

Hi,

I'm creating a couple of matrices in Power BI and I'm trying to create one that has counts by a certain stat and the other will show the percentage. It was easy enough to get the counts (see below). However, I'm having a hard time creating percentages. What I want to do is show the percent of Used or Unused by Fiscal Year by Scholarship Type. So the Unused % for Awareness for FY15 would be 15% and Used would be 85%. How would I go about doing it? I'm fairly new to BI so apologies if this is something simple.

	FY15	FY16
Scholarship Type	Unused	Used	Total	Unused	Used	Total	Grand Total
Awareness	19	108	127	57	461	518	645
Partner	69	686	755	124	1081	1205	1960
Recognition	88	554	642	61	616	677	1319
Regulator	20	280	300	79	382	461	761

Also, here's how the underlying table in the data model is structured

Scholarship Recipient ID	Usage Stats	Fiscal Year	Scholarship Type
4529783	Used	FY15	Recognition
10564207	Used	FY16	Recognition
6409092	Used	FY16	Recognition
30748898	Used	FY15	Recognition
17522706	Used	FY16	Recognition
18329306	Used	FY16	Partner
17310161	Used	FY15	Recognition
21357211	Used	FY16	Awareness

[v-sihou-msft · Accepted solution]

@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

[v-sihou-msft · Accepted solution]

@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

[cwoy2j]

Unfortunately this didn't work. The Usage Stats column in the underlying table is a text field.

Error Message: MdxScript(Model) (1, 62) Calculation error in measure 'ScholarshipData'[Unused Total]: The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String.

[parry2k · Accepted solution]

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

[cwoy2j]

Thanks to both of you!

[cwoy2j]

Thank you! I will try this out when I get back to work next week. Appreciate the response.