Hi,
I've been using PowerBI for a few months at my company but have just started to play around with creating my own measures using DAX and was looking for some help as it's all new to me and what I'm trying to do seems quite daunting to me. I'm going to convert the details of my actual data to an equivalent scenario, using sales as an example, but hopefully it still makes sense...
I have a dataset which includes a CUSTOMERS table (includes name of customer), SALES table (includes order count and order value) and PRODUCTS table (includes product name and product category).
Firstly I'm trying to create a report which shows the top 10 products sold with the number of orders in each product, % of total orders, value of orders in each product, and value as a % of total value. I note that when I use the standard 'Top N' filter the % of orders and % of value only show the % of the top 10, rather than % of grand total.
Then also for each of those 10 products I want to show all the names of customers who ordered them with the same values, i.e. number of orders and value of orders, but I want the percentages to show as a % of the parent row total (e.g. what % of sales in that particular product were ordered by that particular customer).
I don't know if it makes any difference but in terms of how I will be using the report, I'll be filtering the report by product category and producing a separate report for each category. E.g. I will look at 'Stationery' and want a report to show top 10 stationery products and the customers who ordered those products, then I will do the same by filtering to e.g. 'Food' and look at the top 10 food products.
Finally, I intend to export each report to PDF, but the visualisation will not fit on one page, so I was hoping to do the first 5 products on one page, then the next 5 on the next page. I understand that using RANKX may help with this, then just filter the first visualisation to show rank 1-5 and the second to show 6-10, but what would be the DAX for this in my example (I had a go myself but it never seemed to load the visualisation).
Thanks
I've been using PowerBI for a few months at my company but have just started to play around with creating my own measures using DAX and was looking for some help as it's all new to me and what I'm trying to do seems quite daunting to me. I'm going to convert the details of my actual data to an equivalent scenario, using sales as an example, but hopefully it still makes sense...
I have a dataset which includes a CUSTOMERS table (includes name of customer), SALES table (includes order count and order value) and PRODUCTS table (includes product name and product category).
Firstly I'm trying to create a report which shows the top 10 products sold with the number of orders in each product, % of total orders, value of orders in each product, and value as a % of total value. I note that when I use the standard 'Top N' filter the % of orders and % of value only show the % of the top 10, rather than % of grand total.
Then also for each of those 10 products I want to show all the names of customers who ordered them with the same values, i.e. number of orders and value of orders, but I want the percentages to show as a % of the parent row total (e.g. what % of sales in that particular product were ordered by that particular customer).
I don't know if it makes any difference but in terms of how I will be using the report, I'll be filtering the report by product category and producing a separate report for each category. E.g. I will look at 'Stationery' and want a report to show top 10 stationery products and the customers who ordered those products, then I will do the same by filtering to e.g. 'Food' and look at the top 10 food products.
Finally, I intend to export each report to PDF, but the visualisation will not fit on one page, so I was hoping to do the first 5 products on one page, then the next 5 on the next page. I understand that using RANKX may help with this, then just filter the first visualisation to show rank 1-5 and the second to show 6-10, but what would be the DAX for this in my example (I had a go myself but it never seemed to load the visualisation).
Thanks
