cancel
Showing results 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

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

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]))))

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.

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.

Super User

@S_N_L_A , Try a measure like

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors