Forum Discussion

cwoy2j's avatar
cwoy2j
Regular Visitor
9 years ago
Solved

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 ...
Show and Tell
Tutorial Requests
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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

     

     

     

     

  • parry2k's avatar
    parry2k
    9 years ago

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

     

     

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
cwoy2j
Regular Visitor
9 years ago

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's avatar
    parry2k
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      cwoy2j
      Regular Visitor
      9 years ago

      Thanks to both of you!