topic Re: Measure to show above average percentage of dimensions on Card in DAX Commands and Tips

Measure to show above average percentage of dimensions on Card

Anonymous — Tue, 15 Feb 2022 09:54:42 GMT

Hi there,

I am working on a report for a retailer. My Customer wants to see the percentage of channels for which the avg price is above the overall average price. In my sample it would be 40% (4 above/10 below). All based on a Sales table with multiple sales on each channel.

i.e. for illustration on channel 80 of sample data the Average Price is 46.63 (all Revenue divided by Sales) and for all channels it is 51.10 so Channel 80 is below average. I got that part by using Allexcept but don't know how to count my Channels afterwards.

I created sample Data here: https://we.tl/t-gxbtOcNfp1

Now in SQL I would do this with a subquery. Is there a way to do this in DAX? Like for example with Calculatetable as a variable?

Thanks in advance and appreciate recommendations for best practice on asking here. Doing this for the first time.

Best

Chris

Re: Measure to show above average percentage of dimensions on Card

amitchandak — Tue, 15 Feb 2022 10:12:00 GMT

Anonymous , Try a measure like

measure =
var _overall = calculate([Avg Price], allselected()) //use all if needed
return
countx( filter(Values(customer[customer]), [Avg Price] > _overall), [customer])

Re: Measure to show above average percentage of dimensions on Card

Anonymous — Tue, 15 Feb 2022 10:36:21 GMT

Does the trick, thanks! Thought about countx before but could not make it work. Still I'm not 100% sure why this works.

overall computes the overall Average price - so far...

Then Countx counts my Customers (Channels in my example) where Avg Price > overall Average Price (Filter) and groups on Customer (Channel)???