plot median value for selected category

Anonymous — Fri, 30 Oct 2020 15:42:14 GMT

I'm trying to plot the median value of counts for 'SC' category based on selection of 'deposit balance group' category on a bar chart. I created a measure using DAX which can calculate the median values. But it looks like my DAX formula is wrong and it's calculating the median for the entire table and not per category.

For Eg, The median value for $100K-$150K group for 'CD' category should be 36, but its showing 270 on the bar chart.

My sample data:

count	SC	Deposit Balance Group
20	CD	GT $2M
18	CD	$150K-$200K
12	CD	$200K-$250K
42	CD	$200K-$250K
17	CK	$100K-$150K
11	CK	$100K-$150K
11	CK	$100K-$150K
17	CK	$100K-$150K
47	CK	$100K-$150K

The DAX measure I created:

Measure = MEDIANX ( SUMMARIZE ( Sheet1, Sheet1[SC], "Total Count", SUM ( Sheet1[count] ) ), [Total Count] )

Please let me know how can I overcome this situation.

Thanks

I've attached the pbix file which has the complete data and measure formula: pbix file

Re: plot median value for selected category

dedelman_clng — Fri, 30 Oct 2020 16:10:28 GMT

Hi Anonymous -

You're over-thinking it 😊. By doing a Summarize you are totalling all of the counts and then performing MEDIAN on a single value. You would do something similar if you needed to count up the records from raw data, but since you already have the counts, all your measure needs is MEDIAN()

Hope this helps

David

topic Re: plot median value for selected category in DAX Commands and Tips

plot median value for selected category

Re: plot median value for selected category