cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
S_N_L_A
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 startedCompletedDelayed
Europe 66%33%
Asia100%  
Africa50% 50%
Latin America 100% 
Total25%62,5%12,5%

 

RegionStatus
EuropeCompleted

Europe

Delayed
AsiaNot Started
EuropeCompleted
AfricaNot Started
Latin AmericaCompleted
Latin AmericaCompleted
AfricaCompleted

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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! !!

View solution in original post

VahidDM
Super User
Super User

Hi @S_N_L_A 

 

Try this:

 

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

 

 

the output:

VahidDM_0-1635762425360.png

 

 

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

Appreciate your Kudos!!

 

View solution in original post

2 REPLIES 2
VahidDM
Super User
Super User

Hi @S_N_L_A 

 

Try this:

 

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

 

 

the output:

VahidDM_0-1635762425360.png

 

 

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

Appreciate your Kudos!!

 

amitchandak
Super User
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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

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

Top Solution Authors