Forum Discussion

shere100's avatar
shere100
Icon for Helper II rankHelper II
7 years ago
Solved

Simple Calculation required

Hi, I need to do some simple percentage calaculation on two columns.    Not Started 56 Pass 20 Fail 2 Total Work Items 78   I need to display the percentage of success rate. I....
Show and Tell
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi shere100 

    You may create a measure with COUNTX Function.For example:

    Measure 2 =
COUNTX (
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Test Result] = "Pass" ),
    'Table'[Test Result]
)
    / COUNTX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Test Result] = "Not Started" ),
        'Table'[Test Result]
    )

    Regards,

shere100's avatar
shere100
Icon for Helper II rankHelper II
7 years ago

Thanks for the above, much appreciated.

 

The values in the orignal post are based not from the raw data but from the filters selected on a visual and the results displayed

 

How do I then use the measure on the filtered values? If I use the query you have kindly provided me this will not calculate the totals for each status and then measure the totals? 

 

Or how would I calculate the results from the raw data then use the measure calculation you have provided?

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi shere100 

You may try to use ALLSELECTED Function.For example:

Measure =
SUMX (
    FILTER ( ALLSELECTED ( Table2 ), Table2[Status] = "Pass" ),
    Table2[Value]
)
    / SUMX (
        FILTER ( ALLSELECTED ( Table2 ), Table2[Status] = "Not Started" ),
        Table2[Value]
    )

Regards,

  • shere100's avatar
    shere100
    Icon for Helper II rankHelper II
    7 years ago

    Hi, thanks for the response. I get the following message.

     

    Error Message:
    MdxScript(Model) (28, 5) Calculation error in measure 'Test Result 2'[Measure 6]: The function SUMX cannot work with values of type String.

     

    To clarify I need to calculate the sum for a status that has string values.  The above message appears when I attempt to run the measure.

    v-cherch-msft wrote:

    Hi shere100 

    You may try to use ALLSELECTED Function.For example:

    Measure =
SUMX (
    FILTER ( ALLSELECTED ( Table2 ), Table2[Status] = "Pass" ),
    Table2[Value]
)
    / SUMX (
        FILTER ( ALLSELECTED ( Table2 ), Table2[Status] = "Not Started" ),
        Table2[Value]
    )

    Regards,

     

     

     

    • shere100's avatar
      shere100
      Icon for Helper II rankHelper II
      7 years ago

      The formula works for numbers in a column but I need to calculate the sum value of string values in a column. 

       

      Please advise.

    • v-cherch-msft's avatar
      v-cherch-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi shere100 

      The error is because the Table2[Value] column's type is text.Please check if this [Value] column's type is whole number.If it is not your case,please paste the simplified data sample and expected output like below article 

      How to Get Your Question Answered Quickly.

      Regards,

      • shere100's avatar
        shere100
        Icon for Helper II rankHelper II
        7 years ago

         

        Hi, I am trying to do a count on Test Result column where the total = 'pass' divide by the count of the Test Result column where the total = 'not started' to give the percentage.