DAX Pourcentage Calculation for text format

[dofrancis3]

Dear Colleagues,

Please i need your help !

According to the table below, at "% Status" column i would like to calculate percentage of status  (Fail and Pass)

The formula is: Fail or Pass divise by total Fail and pass

Here the my DAX mesure for calculed the total nbr of satatus: 

Tot number of Status =

VAR __numberOfInterwd =

CALCULATE (

    COUNTROWS ( repository ),

    ( repository[STATUS] = "Pass"

        || repository[STATUS] = "Fail" )

)

RETURN

IF(ISBLANK(__numberOfInterwd), "No Data",__numberOfInterwd)

Country	Staus	% Status
CD	Fail
ME	Pass
CD
CD	Pass
ME	Fail
CD	Pass
ME	Pass
CD	Fail
CD	Pass
CD	Fail
CD	Pass
ME
CD	Pass
CD	Pass
ME	Fail
CD	Pass
CD	Fail

[Anonymous]

Hi, @dofrancis3 

Based on your description, I've created the following calculation table formula:

Percentage = 
VAR _status_fail = CALCULATE(COUNTROWS('repository'),FILTER('repository','repository'[Country]=EARLIER(repository[Country])&&'repository'[Staus]="Fail"||'repository'[Staus]="Pass"))
VAR _total_status = CALCULATE(COUNTROWS('repository'),FILTER(ALL(repository),'repository'[Staus]="Fail"||'repository'[Staus]="Pass")) 
RETURN IF(NOT ISBLANK('repository'[Staus]),FORMAT(DIVIDE(_status_fail,_total_status),"0.0%"))

This variable _total_status used to count the total number of rows in the table that passes and fails. _status_fail is used to find the number of pass and fail rows in the current country. Use IF to determine that the current status is not empty, and then find the percentage of pass and fail. The result is formatted as a percentage in text format via the format function.

Here are the results:

I'm not sure I fully understand what you mean. If you don't understand something, can you describe the output you expect based on the sample data you provide? I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dofrancis3]

Dear @Anonymous thank you for this but please could you let me know how can i change the percentage in "Whole or Percentage format" because the percentage is in text format and i can't make any chart. 

Please suport 

[Anonymous · Accepted solution]

Hi, @dofrancis3 

Thank you very much for your reply. First, you need to change the expression of the calculated column to:

Percentage = 
VAR _status_fail = CALCULATE(COUNTROWS('repository'),FILTER('repository','repository'[Country]=EARLIER(repository[Country])&&'repository'[Staus]="Fail"||'repository'[Staus]="Pass"))
VAR _total_status = CALCULATE(COUNTROWS('repository'),FILTER(ALL(repository),'repository'[Staus]="Fail"||'repository'[Staus]="Pass")) 
RETURN IF(NOT ISBLANK('repository'[Staus]),DIVIDE(_status_fail,_total_status))

In the top navigation bar, format this column without percentages:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.