Forum Discussion

epmck11's avatar
epmck11
Frequent Visitor
8 years ago
Solved

Problem dividing one column by another

I'm very new to Power BI, so please go easy on me here. I am trying to calculate scanning percentage based on branch. So my data would look something like this:

 

Branch Electronic Signature

ABC     Y

DEF     Y

ABC     N

 

The idea is if product has an electronic signature, it would be a "Y", if not, it would be a "N". I created an additional column that says:

 

Scanned = IF (Query1[Electronic Signature] = "Y", 1, 0)  

 

This works and allows me to turn the scanned into a numeric so I can figure out the percentage. So I want to figure out what percentage of stops were scanned, so I create another measure:

 

Scan Percent = sum(Query1[Scanned]) / countrows(Query1)

 

But I am getting data that matches nothing like I am expecting. What am I doing wrong? What is a better way to do this? Please help and I'm sorry for the newb question! 

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi epmck11,

     

    According to your description, you want to calculate the percent of signed records of current type, right?

    Percent of current Branch =
    CALCULATE (
     COUNTA ( 'Table'[Electronic Signature] ),
     'Table'[Electronic Signature] = "Y",
     VALUES ( 'Table'[Name] ),
     ALLSELECTED ( 'Table' )
    )
     / CALCULATE ( COUNTROWS ( ALLSELECTED ( 'Table' ) ), VALUES ( 'Table'[Name] ) )

    Percent of all Branch=
    CALCULATE (
     COUNTA ( 'Table'[Electronic Signature] ),
     'Table'[Electronic Signature] = "Y",
     VALUES ( 'Table'[Name] ),
     ALLSELECTED ( 'Table' )
    )
     / COUNTROWS ( ALLSELECTED ( 'Table' ) )

     

    Regards,

    Xiaoxin Sheng

5 Replies

  • epmck11's avatar
    epmck11
    Frequent Visitor
    8 years ago

    I'm very new to Power BI, so please go easy on me here. I am trying to calculate scanning percentage based on branch. So my data would look something like this:

     

    Branch Electronic Signature

    ABC     Y

    DEF     Y

    ABC     N

     

    The idea is if product has an electronic signature, it would be a "Y", if not, it would be a "N". I created an additional column that says:

     

    Scanned = IF (Query1[Electronic Signature] = "Y", 1, 0)  

     

    This works and allows me to turn the scanned into a numeric so I can figure out the percentage. So I want to figure out what percentage of stops were scanned, so I create another measure:

     

    Scan Percent = sum(Query1[Scanned]) / countrows(Query1)

     

    But I am getting data that matches nothing like I am expecting. What am I doing wrong? What is a better way to do this? Please help and I'm sorry for the newb question! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      epmck11,

      I  use your measure to create the following table, does it return your expected result? If not, please post your desired result.



      Regards,
      Lydia

      • epmck11's avatar
        epmck11
        Frequent Visitor
        8 years ago

        Yes, that would be my expected result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi epmck11,

     

    According to your description, you want to calculate the percent of signed records of current type, right?

    Percent of current Branch =
    CALCULATE (
     COUNTA ( 'Table'[Electronic Signature] ),
     'Table'[Electronic Signature] = "Y",
     VALUES ( 'Table'[Name] ),
     ALLSELECTED ( 'Table' )
    )
     / CALCULATE ( COUNTROWS ( ALLSELECTED ( 'Table' ) ), VALUES ( 'Table'[Name] ) )

    Percent of all Branch=
    CALCULATE (
     COUNTA ( 'Table'[Electronic Signature] ),
     'Table'[Electronic Signature] = "Y",
     VALUES ( 'Table'[Name] ),
     ALLSELECTED ( 'Table' )
    )
     / COUNTROWS ( ALLSELECTED ( 'Table' ) )

     

    Regards,

    Xiaoxin Sheng