Simple Calculation required

[shere100]

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.e the calculation would be 20 (pass) / 56 (not started) * 100.

Can you please talk me through the process. Thanks 

[v-cherch-msft]

Hi @shere100 

You may create a measure and then set the measure's format %.

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

Regards,

[shere100]

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]

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]

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,

---

[v-cherch-msft]

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]

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.

[shere100]

Is the above possible to do? or do I need to create an if statement on new column if the data from test Result = 'pass' then place a 1 otherwise place 0, then to do a count on all '0' and '1'. Then create a new meaure and complete the calculation? 

If the above method is correct how do I create a new column from the data set from the query? or do I create a new table and then point to the table that has the source data?

[v-cherch-msft · Accepted solution]

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]

For the measure that you kindly provided me with, can you please provide an example  of an if statement from the column 'Teams' = 'Content Support' to then run the measure calculation.

I need to embed the IF statement where the data is applicable to a specific team only.

[v-cherch-msft]

Hi @shere100 

You may create a measure like below.If you need other help,I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.Kindly mark my answer as a solution for this thread.

Measure =
COUNTX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Test Result] = "Pass"
            && 'Table'[Team] = 'Content Support'
    ),
    'Table'[Test Result]
)

Regards,

[shere100]

Apologies,

Currently the measure is 'Pass' / 'Not Started'.

How would I amend so that it is a'Pass' / 'Not Started + 'Pass' and incorporate in to the measure?

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

[shere100]

Hi, can you please advise?

[v-cherch-msft]

Hi @shere100 

You could use || .For example:

Table2[Status] = "Not Started"||Table2[Status] = "Pass"

Regards, 

[shere100]

Many thanks , the calculation is now showing correctly.

[shere100]

Thats absolutely brilliant, that works perfectly. 

Final question, if I have a visual from one query can I use that query to add an another visual i.e for the measure I have created? or do I need to create a seperate query and then insert the measure?

[shere100]

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

Please advise.