Matrix %RT% (few questions)

[Anonymous]

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. 

[amitchandak]

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]

Hi, 

My data looks similar to this:

Store Banner	Planned Delivery Date	Actual Delivery Date	Delivery Status
Store 1	12/02/2020	12/02/2020	On Time
Store 2	13/02/2020		Pending
Store 1	13/02/2020	15/02/2020	Late
Store 2	14/02/2020	14/02/2020	On Time
Store 1	13/02/2020	12/02/2020	Early
Store 1	14/02/2020	14/02/2020	On Time
Store 2	13/02/2020	14/02/2020	Late
Store 2	12/02/2020	12/02/2020	On Time
Store 2	11/02/2020	11/02/2020	On Time
Store 2	10/02/2020		Pending
Store 2	9/02/2020	10/02/2020	Late

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

Banner	Early	On Time	Pending	Late	Total
Store 1	1 (25%)	2 (50%)	0 (0%)	1 (25%)	4 (100%)
Store 2	0 (0%)	3 (42%)	2 (28%)	2 (28%)	7 (100%)

I have it looking like so:

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:

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]

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.

3. Create the matrix like below.

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

Then the measure could work.

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]

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]

Hi @Anonymous ,

Have you solved this problem?

Best Regards,

Icey

[Baskar]

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]

Current formula is listed as above, a basic count.

COUNT(Table[Status])

[Baskar]

1. for 1dp

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/