Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to get Overall % in a table visual

I have a table as shown below:

I want to add a measure to the table visual that calculate overall % of Approved status

Total milestone = 6

Milestone with Approved Status =2

Overall % = 2/6 = 33%

Hence, what i want to achieve is shown below

Thanks

 

 

Need Help
Tips and Tricks
  • Pragati11's avatar
    Pragati11
    4 years ago

    Hi Anonymous ,

     

    You can achieve this with the help of a measure.

    Input data taken:

    Now create a DAX measure:

    Overall = 
var numerator = CALCULATE(COUNTROWS(Milestone), ALLEXCEPT(Milestone, Milestone[Status]))
var denominator = CALCULATE(COUNTROWS(Milestone), ALL(Milestone))
var overall = (DIVIDE(numerator, denominator, 0))/2
var statusVal = SELECTEDVALUE(Milestone[Status])
RETURN
IF(
    statusVal = "Approved", overall, " "
)

    Now move this to a table visual and you get the required result:

    I have defined 3 decimal places for the Overall measure. This can be modified easily.

     

5 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    You can achieve this with the help of a measure.

    Input data taken:

    Now create a DAX measure:

    Overall = 
var numerator = CALCULATE(COUNTROWS(Milestone), ALLEXCEPT(Milestone, Milestone[Status]))
var denominator = CALCULATE(COUNTROWS(Milestone), ALL(Milestone))
var overall = (DIVIDE(numerator, denominator, 0))/2
var statusVal = SELECTEDVALUE(Milestone[Status])
RETURN
IF(
    statusVal = "Approved", overall, " "
)

    Now move this to a table visual and you get the required result:

    I have defined 3 decimal places for the Overall measure. This can be modified easily.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Pragati11 - Firstly, I want to check only Approved status. 

      And Secondly, I want to divide count of Approved status by count of Milestone to get overall %

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        4 years ago

        HI Anonymous ,

         

        Check my previous response with the solution.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Pragati11  - Thanks for the reply. It worked. However, the requirement is to remove the Status column, if it doesn't work. And if there are multiple Milestone with different status, it work work. 
      For example, this has multiple milestone with different status.

      The achieve outcome should look like this below:

      sorry for confusion

       

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        4 years ago

        Hi Anonymous ,

         

        A little more clarity on your question earlier must have been appreciated.

        What should be the output for % now as you have Approved status multiple times appearing now? It's not just 2 times as before.

        Please provide me a proper output required rather than just showing % signs as the output.