MDX help! How to group by measure value ranges and use these groups in columns
I have a salesman dimension and sales fact table and some data as below:
salesman sales_amount
-----------------------------
Mike $200
Phil $5
John $280
Jessica $550
I'd like MDX query result as below:
sales_range Count
-------------------------
<$100 1
$100-$500 2
>$500 1
The sales_range (<$100, $100-$500 and >$500) are fixed and hard-coded and never needed to be changed. And I can do dimension/fact table or cube changes for this if needed.
Please help! How to write MDX query for this?
Any input will be highly appreciated! Thanks!