Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
@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])
@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])
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)???
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |