cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## DAX Average based on text - AVG grouped by other column value

Hi!

I'm certain that there is an answer to this that I've stumbled upon before. But after almost 2 days of searching (probably my searching skills that are lacking) I'll have to post a question.

I have a table where two of the columns are Region and Status.

What I'd like to do is to create a DAX which calculates the average count of "Status" per "Region" as well as total.

In other words the end result (that I want to have as a visualization) should be:

 Not started Completed Delayed Europe 66% 33% Asia 100% Africa 50% 50% Latin America 100% Total 25% 62,5% 12,5%

 Region Status Europe Completed Europe Delayed Asia Not Started Europe Completed Africa Not Started Latin America Completed Latin America Completed Africa Completed

Thanks in advance!

2 ACCEPTED SOLUTIONS
Super User

@S_N_L_A , Try a measure like

divide(counrows(Table), calculate(counrows(Table), filter(allselected(Table), Table[Region] = max(Table[Region]))))

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Super User

Hi @S_N_L_A

Try this:

``````Measure =
COUNTROWS ( 'Table' )
/ CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( 'Table'[Status] ) )``````

the output:

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

Appreciate your Kudos!!

2 REPLIES 2
Super User

Hi @S_N_L_A

Try this:

``````Measure =
COUNTROWS ( 'Table' )
/ CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( 'Table'[Status] ) )``````

the output:

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

Appreciate your Kudos!!

Super User

@S_N_L_A , Try a measure like

divide(counrows(Table), calculate(counrows(Table), filter(allselected(Table), Table[Region] = max(Table[Region]))))

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

## Helpful resources

Announcements

#### Power BI May 2023 Update

Find out more about the May 2023 update.

#### Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors