Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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. 

Need Help

8 Replies

  • Baskar's avatar
    Baskar
    Resident Rockstar
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Current formula is listed as above, a basic count.

       

      COUNT(Table[Status])

      • Baskar's avatar
        Baskar
        Resident Rockstar
        6 years ago

        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/

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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:

       

      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's avatar
        Icey
        Community Support
        6 years ago

        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.