Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Matrix %RT% (few questions)

 

 

My matrix is as below.

I use the measure "Count = Count(Table[Delivery Status)" there twice.

  • FIRST as a normal number 
  • SECOND as a percentage of row total

    Its working like intended. However my manager only wants 1dp for the percentage. I have no clue how to change this as I didnt write the measure myself and used the built in function. 

     

    Does anyone know how I can make it only show 1dp? I did try the modelling method, to manually change it but I cannot go below 0 for the whole number measure.

    Is there a way I can write my own dax code to count by row total like the built in function?

     

    Also, how can I remove the last sub total 100% but still show the total count. I need to save that space for another visual if possible, its fairly intuiative that the total of a percentage is 100%

     

    Thank you in advance. 

row percent.PNG

8 REPLIES 8
amitchandak
Super User
Super User

In the latest release for matrix, if you right-click on the measure in visualization Tab, there is option % of row total, Are you looking for that?

Anonymous
Not applicable

Hi, 

My data looks similar to this:

Store BannerPlanned Delivery DateActual Delivery DateDelivery Status
Store 112/02/202012/02/2020On Time
Store 213/02/2020 Pending
Store 113/02/202015/02/2020Late
Store 214/02/202014/02/2020On Time
Store 113/02/202012/02/2020Early
Store 114/02/202014/02/2020On Time
Store 213/02/202014/02/2020Late
Store 212/02/202012/02/2020On Time
Store 211/02/202011/02/2020On Time
Store 210/02/2020 Pending
Store 29/02/202010/02/2020Late

 

My manager has requested the out put (matrix visual) to be something like this:

BannerEarlyOn TimePendingLateTotal
Store 11 (25%)2 (50%)0 (0%)1 (25%)4 (100%)
Store 20 (0%)3 (42%)2 (28%)2 (28%)7 (100%)

 

I have it looking like so:

row percent.PNG

 

That is close to what my manager is wanting and if i hide the headings and make my own title i get really close to the end result:

Capture.PNG

 

Now I have another problem. I cannot show 0% for rows that have blank which I have use if statement to place 0 there already.

Would you know a work around for this? I also do not know how to make brackets around my precentages

Icey
Community Support
Community Support

Hi @Anonymous ,

 

The measure doesn't work because that there is no complete data for Delivery Status

 

Try this:

 

1. Please create a calculated table with Values to get the Delivery Status.

Status = VALUES('Table'[Delivery Status])

 

2. Create the relationship between the two tables.

status.jpg

 

3. Create the matrix like below.

Count = COUNT('Table'[Delivery Status])+0

Then the measure could work.

status2.jpg

 

 

 

Best Regards,

Icey

 

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

Anonymous
Not applicable

Thank you for your detail reply. 

 

I think this method would work, however I think it is because for stores that dont exist for some at certain location, it can not show 0% because there was is nothing to calculate.  If I remove my page filter for location it works fine as all stores have status values.  

This might need me to work with ISFILTER function.. I will have another play around.

 

Cheers,

Haley

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Have you solved this problem?

 

 

Best Regards,

Icey

Baskar
Resident Rockstar
Resident Rockstar

dear friend, 

share the formula which you have, 

we can't hide the last one column alone, but we can hide the last two column. why because we have option to hide the totals and sub totals.

 

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

http://powerbi.baskarbiconsultant.com/

Anonymous
Not applicable

Current formula is listed as above, a basic count.

 

COUNT(Table[Status])

1. for 1dp

Step 1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you want to create new measure you can create with create measure.

 

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

http://powerbi.baskarbiconsultant.com/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors