Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Row % DAX

  Hello DAX Experts,   I am having a little trouble making DAX work as I envision it.   My expected result is the top table - Built-In Function.   For my bottom table, I am using DAX to have m...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi tamerj1 ,

     

    That is exactly what I am trying to do.

     

    Thank you for adding the PBIX file. 

     

    I think my Power BI might be buggy. I am puzzled as to why I get 100% in all the rows. Your PBIX file works exactly as I envision. My IT is not well versed in Power BI debugging. Maybe I should notify MS.

     

    At any rate, thank you very much!

     

     

v-xiaotang's avatar
v-xiaotang
Community Support
3 years ago

Hi Anonymous 

Thanks for reaching out to us.

Not sure, the result in this picture is what you need, my understanding is that you have found the correct measure?

 

 

Best Regards,

Community Support Team _Tang

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi v-xiaotang ,

     

    Yes, you are spot on. I think my Power BI might be buggy. I am going to reach out to MS. The best my IT team can do is maybe upgrade my version.

     

    I sincerely appreciate your guidance.

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi v-xiaotang and tamerj1 ,

       

      I've found the issue.  Sorting Rating by Rating Number is the issue.

       

      Rating is a conditional column to allow sorting Rating numerically instead of alphabetically. But it seems to have an unintended side effect with DAX. The % is made 100% across the table.

       

      When Rating is sorted by Rating, % is across the rows - my intended outcome. 

       

      For etiquette, it would make sense to post a new question about sorting and DAX, correct?

       

      I appreciate your guidance so very much.

       

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        tamerj1  v-xiaotang 

         

        Thank you for your guidance.

         

        I found the last piece of the puzzle.

         

        I found a solution.

         

        Able % of Row Total =

        DIVIDE (

            COUNT ( Able[Ability] ),

            CALCULATE ( 

                COUNT ( Able[Ability] ),

                ALL ( 'Able'[Rating], 'Able'[Rating Number] )

            )

        )

         

        To have the sort column not interfere with the DAX, rating number must be added to the ALL statement.

         

        https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/

         

        The calculation works as envisioned.