Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
frknklcsln
Helper II
Helper II

how to get a total of a value in a dax power bi

Hi,

 

I have a measure that calculates the count of a categoric value.

 

frknklcsln_1-1666608902639.png

 

I want to calculate the overall percentage of this measure. 

 

For example;

1/5422

15/5422

2/5422 and so on..

 

Here is my DAX;

 

Measure 2 = 
var reason = CALCULATE(COUNT('Table'[Reason]))

return
reason

 

I can't write the var function that will always be 5422. Its the total countrows of the ID column.

 

Thanks in advance.

 

 

 

2 ACCEPTED SOLUTIONS

You are wright, it cant be in a variable.

Please do this

 

% of Count = 
    DIVIDE(
        COUNTROWS(Table),
        CALCULATE(COUNTROWS(Table),
            ALL(table)
            )
        )

 

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

View solution in original post

I used ALLSELECTED()  an it worked. Thanks Jose big time.

View solution in original post

10 REPLIES 10
jcalheir
Solution Supplier
Solution Supplier

Hi

 

You need the folowing measure

 

% of Count = 

var _count = COUNTROWS(Table[Reason])

RETURN
    DIVIDE(
        _count,
        CALCULATE(_count,
            ALL(table)
            )
        )

 

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Hi Jose,

 

Thank you for the response.

 

Unfortunately,

 

I have an error in this row of the DAX:

var _count = COUNTROWS(Table[Reason])

Because countrows only takes table as a function.

 

frknklcsln_0-1666611016506.png

 

 

 

My bad. Then count the table rows

 

% of Count = 

var _count = COUNTROWS(Table)

RETURN
    DIVIDE(
        _count,
        CALCULATE(_count,
            ALL(table)
            )
        )

 

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Hi again,

 

Row based, it is always will be 1/1, 15/15, and so on . So, I get always %100. It doesnt get the total row count which is 5422. Total row count shouldnt change for every row.

With that measure it should't. Have you tried it?

ALL() function  should remove all the filter contex and return the count of all rows.

Yes, I tried it. I am adding this measure to a table. Could that be the issue? Because the main idea is the give perc of the specific row's category.

 

 

You are wright, it cant be in a variable.

Please do this

 

% of Count = 
    DIVIDE(
        COUNTROWS(Table),
        CALCULATE(COUNTROWS(Table),
            ALL(table)
            )
        )

 

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Nice one, Zé! 🙂

It seems working but need a little bit more. Can we get the total rows with the selected filters on the table?

 

Lets say without filters we have 120k, but with filters we have 5422. Should I use KEEPFILTERS?

I used ALLSELECTED()  an it worked. Thanks Jose big time.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.