Forum Discussion

dofrancis3's avatar
dofrancis3
Icon for Resolver I rankResolver I
2 years ago
Solved

DAX Pourcentage Calculation for text format

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)

 

CountryStaus% Status
CDFail 
MEPass 
CD  
CDPass 
MEFail 
CDPass 
MEPass 
CDFail 
CDPass 
CDFail 
CDPass 
ME  
CDPass 
CDPass 
MEFail 
CDPass 
CDFail 
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      dofrancis3
      Icon for Resolver I rankResolver I
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.