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!

     

     

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.