topic Re: DAX Average based on text - AVG grouped by other column value in DAX Commands and Tips

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

S_N_L_A — Mon, 01 Nov 2021 09:33:17 GMT

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!

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

amitchandak — Mon, 01 Nov 2021 10:23:14 GMT

S_N_L_A , Try a measure like

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

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

VahidDM — Mon, 01 Nov 2021 10:28:01 GMT

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!!