Need help calculate the percentage based on certain criteria using DAX

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Using the Format function, it can be converted into a specified format, but the final display method is displayed in the format of Text

FORMAT function (DAX) - DAX | Microsoft Learn

So if you still want to use [Open percent] for aggregation and other operations, there will be a format mismatch error

Measure =[Open percent] +1

Here are the steps you can follow：

1. Create measure.

Count Open item =
COUNTX(FILTER(ALL(Table2),[Last status]="open"),[Last status])

Count total item =
COUNTX(ALL(Table2),[Last status])

Open percent =
DIVIDE([Count Open item],[Count total item])

2. You click [Open percent] - Measure tools - click %, it will be displayed in the format of %, and you can directly fill in the number to show how many digits after the decimal point.

3. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[Anonymous]

Dear v-yangliu-msft,

Thank so much. I really appreciate it. 
I works fine. 

Thank you

Prima Indonesia

[Anonymous]

Dear all,

I am using Power BI Desktop. 
I have a table (Table2) with column header "Last status", which contains "open" and "close" textes.
I do have a calculation of how many item "open" and "close". But I need to present also the percentage of item "open" and "close" in a card.

Can someone could help me with this subject, please?

Thanks so much for your helping hands.
Prima - Indonesia

[Anonymous]

Open=Calculate(count(table[status]),status="open")

Count all= count(table[status])

Percentsge=Divide([open],[count all],0)

[Anonymous]

Dear eliasayy,

Thanks a lot for your kind and help. 
I've tried your suggestion, it gave me an error. 

But then I make a calculation with measure by dividing total "Open" with total item ("Open" + "Close")
Before I wrote the question, I made 3 measures:
1. Count total item 
2. Countif of "Open"
3. Countif of "Close"

Then I made the fourth measure, to find the percentage by dividing countif "Open" to count total item.

Open percent = 
    FORMAT(DIVIDE([Count Open item],[Count total item]),"0%")

The DAX FORMAT I found in this great community also.
https://community.powerbi.com/t5/Desktop/Card-Conditional-Formatting-Measure-Percentage/m-p/925548

Anyway, thanks so much for your help. I am really appreciate it.
Prima - Indonesia

[Anonymous]

Im glad it worked out even though i dont understand why it gave you an error:

Open = CALCULATE(COUNT('Status'[Status]),'Status'[Status]="Open")
Total = CALCULATE(COUNT('Status'[Status]),ALL('Status'[Status]))
Percentage = DIVIDE([Open],[Total],0)

 

[Anonymous]

Dear eliasayy,

This the error message when I try your suggestion, but somehow I believe that you were right. 
Perhaps the mistake in on me, since I am a newbie. Let me be honest, I am starting using Power BI around 7 days ago.

Total = CALCULATE(COUNT(Table2[Last Status]),ALL(Table2[Last Status]))

The syntax for 'Total' is incorrect.

Somehow, I will need this calculation in the future, and I hope you will come back me to again with another finding, what is my mistake. 

Thank so much, I really appreciate it. 
Prima Indonesia

[Anonymous]

Hmm perhaps send a screenshot of the error. And it totally ok we all started as newbies hehe

[Anonymous]

Hmm perhaps send a screenshot of the error. And it totally ok we all started as newbies hehe