Calculate Percentage of Non Numeric(text) Column

[spriyanshu]

I have a Calculated Column, which represent invoice passed or failed against Invoice Numbers. This is a text cloumn with field value as "Success" or "Failure" which is a text datatype.
What i am trying to acheive is how many invoices submitted succesfully in the duration and Percentage of it.

Using simple date slicer to select duration. 

[Anonymous]

Hi @spriyanshu,

If the date column of the data table is used in the DAX expression of the status column, please consider creating a calendar table and a relationship with the data table. and use the date column from the calendar table instead of the data table date column in the expression and slicer.

Measure.

Success count = 
VAR _value = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Status]="Success"))
RETURN
IF(_value<>0,_value,0)

Success ratio = 
VAR _value = CALCULATE(COUNTROWS('Table'))
RETURN
DIVIDE([Success count],_value)

Attach the PBIX file for reference. Hope it helps.

If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

Here are some references that may be helpful.

How to Get Your Question Answered Quickly

How to provide sample data

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[spriyanshu]

Thanks for the solution, but what i was looking for is when i change the date filter to yearly or monthy, it will calculate number of success in "SUPP Benchmark" field and gives it percentage in success against total number of success and failure in same field and show it in form of Card Visual

[Anonymous]

Hi @spriyanshu ,

I'm still not sure what you want, I created a simple for testing.

Suppose the slicer's field content is April 2022.

Count the number of successes in the "SUPP Benchmark" field.

Success count = 
VAR _value = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Status]="Success"))
RETURN
IF(_value<>0,_value,0)

percentage in success against total number of success and failure in same field.

Ratio = 
VAR _value = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[SUPP Benchmark]="failure"))
RETURN
DIVIDE([Success count],_value)

Chang the format of the measure.

Attach the PBIX file for reference. Hope it helps.

If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

Here are some references that may be helpful.

How to Get Your Question Answered Quickly

How to provide sample data

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[spriyanshu]

This is just the idea i am giving about the benchmark, the actual calcualtion again depend on different calculated columns

Dax for Benchmark: 

Benchmark = if ('Table'[Status] = "Success", "Success", "Benchmark Crossed")

SupplierDateStatus A 3/25/2021 Success B 3/25/2021 Failure C 3/25/2021 Success D 3/25/2021 Success E 8/25/2021 Success F 8/25/2021 Failure G 8/25/2021 Success H 8/25/2021 Failure I 8/25/2021 Success J 8/25/2021 Failure K 3/25/2021 Success L 8/25/2021 Success M 6/25/2021 Failure N 6/25/2021 Success O 6/25/2021 Failure P 6/25/2021 Success Q 11/25/2021 Success R 6/25/2021 Failure S 6/25/2021 Success T 6/25/2021 Success U 11/25/2021 Failure V 6/25/2021 Success W 11/25/2021 Failure X 11/25/2021 Success Y 11/25/2021 Success Z 11/25/2021 Failure

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

I hope you can get an idea from this sample file about how to create measures for your data model.

Success count: = 
CALCULATE ( COUNTROWS ( Data ), Data[Status] = "Success" )

Success ratio: = 
DIVIDE ( [Success count:], COUNTROWS ( Data ) )

[spriyanshu]

Hi Jihwan
I tried this already, but its throwing an error of "circular dependency"

In My data model only one fact table is there, Column which represent "Success" and "Failure" is a calculated column

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[Jihwan_Kim]

Hi,

If it is OK, please share your sample pbix file, and then I can try to come up with a more accurate solution.

Thank you.