cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
quipmaster
Helper II
Helper II

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 more control

 

My DAX is 

Able % of Row Total = DIVIDE(
COUNT(
Able[Ability]),
CALCULATE(
COUNT(
Able[Rating] ) ,
ALL(Able[Ability])))

 

However, the Total column is not 100%, but 12.5%. Also, the row percentages are different.  

 

quipmaster_0-1664582698924.png

 

I am not a community super user, so I can only post this link to the spreadsheet  https://docs.google.com/spreadsheets/d/16V_02xrT1Xi1SLbigs11q14EgIfQCtUVW1efR7axk-M/edit?usp=sharing as opposed to a PBIX. All nudges are greatly appreciated.

 

Thank you,

 

1 ACCEPTED SOLUTION

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!

 

quipmaster_0-1664806780236.png

 

View solution in original post

8 REPLIES 8
v-xiaotang
Community Support
Community Support

Hi @quipmaster 

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?

vxiaotang_0-1664793807176.png

 

 

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.

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.

 

quipmaster_1-1664807096102.png

 

 

 

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.

 

quipmaster_1-1664824421651.png

 

 

 

 

 

@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.

 

quipmaster_0-1664835477170.png

 

tamerj1
Super User
Super User

Hi @quipmaster 
I hope this is what you're looking for

1.png

Able % of Row Total = 
DIVIDE (
    COUNT ( Able[Ability] ),
    CALCULATE (  
        COUNT ( Able[Ability] ),
        ALL ( Able[Rating] ) 
    )
)

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!

 

quipmaster_0-1664806780236.png

 

amitchandak
Super User
Super User

@quipmaster , Why are taking the rating below?

Like ?

 

Able % of Row Total = DIVIDE(
COUNT(
Able[Ability]),
CALCULATE(
COUNT(Able[Ability] ) ,
removefilters(Able[Rating])))

Hi @amitchandak,

 

Thank you for your reply. Maybe these two tables will better explain my logic.

 

With the Total Abilities per Rating DAX, I want to figure out a way to divide Rating by Sum responses. For examples, Analyze data to measure success and extremely difficult has one response. 1 response divided by 161 responses equals 0.62% not .08%. 

 

quipmaster_1-1664592961181.png

 

 

I tried the DAX I think you recommended.  This is the output.

 

quipmaster_2-1664594538148.png

 

quipmaster_0-1664592707055.png

 

Please let me know If you have any questions.

 

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors