Forum Discussion
Divide one column by another in a matrix
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 |
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"))
5 Replies
v-sihou-msftMicrosoft Employee
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
Thank you! I will try this out when I get back to work next week. Appreciate the response.
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
Super User
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"))
