cancel
Showing results for
Did you mean:
Helper III

Calculation of Row total as Percentage

I want to get row total as percentage only not whole row as percentage in a matrix. But I want the percentage based on row filled or not if there are two cells in a row  and both cell are filled then it should show row total as 100% and if one cell is filled and  other is empty it should show 50% similarly out of three cells if two cells are filled row total should be 33%.

I am attaching the data as well as the result I am getting along with desired outcome. Hoping for the answer.

Thanks

 Date Names 02/03/2021 John 02/03/2021 Aliana 02/03/2021 Amily 02/03/2021 Faby 02/03/2021 John 02/03/2021 Aliana 02/03/2021 Amily 02/03/2021 Aliana 02/03/2021 Amily 02/03/2021 Faby 03/03/2021 Faby 03/03/2021 Sheam 03/03/2021 Aliana 03/03/2021 Amily 03/03/2021 Aliana 03/03/2021 Amily

1 ACCEPTED SOLUTION
Super User

@Hamdan1234 this will do it,

``````Measure =
VAR __total = CALCULATE ( DISTINCTCOUNT ( data[Date] ), ALLSELECTED ( ) )
VAR __name = CALCULATE ( DISTINCTCOUNT ( data[Date] ), REMOVEFILTERS ( data[Date] ) )
RETURN
DIVIDE ( __name, __total )``````

@amitchandak  measure will need change.

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

3 REPLIES 3
Super User

@Hamdan1234 this will do it,

``````Measure =
VAR __total = CALCULATE ( DISTINCTCOUNT ( data[Date] ), ALLSELECTED ( ) )
VAR __name = CALCULATE ( DISTINCTCOUNT ( data[Date] ), REMOVEFILTERS ( data[Date] ) )
RETURN
DIVIDE ( __name, __total )``````

@amitchandak  measure will need change.

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@Hamdan1234 , a measure like

divide(countrows(Table), calculate(countrows(Table), removefilters(Table[Date]))

Helper III

I have modified it little bit after seeing that this measure was showing in each column so keep it to one at last I have made it like below:

Measure2 =
VAR __total = CALCULATE ( DISTINCTCOUNT ( Sheet1[Date] ), ALLSELECTED ( ) )
VAR __name = CALCULATE ( DISTINCTCOUNT ( Sheet1[Date] ), REMOVEFILTERS ( Sheet1[Date] ) )
RETURN
IF(
ISFILTERED(Sheet1[Date]),
BLANK(),
DIVIDE ( __name, __total ))

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors