Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello community!
This is my first post here, hope I do this correctly 🙂
I have a huge table with sales data from my customers.
In this table i have several products, product groups, weeks, customers, etc.
I get one row pr. item/customer/week with my revenue, and the total revenue of the Item Group pr. customer and week.
I want to find two things:
1. The total amount of revenue, based on my selection of slicers in my report
2. My share of revenue pr. product
My problem here is that whenever I have a revenye on my Item, the "Total Item group revenue" shows up, and is therefore repeating itself several times.
This is what my table look like, I have only put up one week in this example (mye table contains a lot more columns and rows, but these are the important ones):
Week | Customer | Item | Item group | My revenue | Revenue total Item group in store |
Week 1 | North | Product A | Yellow | 50 | 10 000 |
Week 1 | North | Product B | Yellow | 100 | 10 000 |
Week 1 | North | Product C | Yellow | 200 | 10 000 |
Week 1 | North | Product D | Blue | 200 | 5 000 |
Week 1 | North | Product E | Blue | 50 | 5 000 |
Week 1 | South | Product A | Yellow | 20 | 6 000 |
Week 1 | South | Product B | Yellow | 40 | 6 000 |
Week 1 | South | Product C | Yellow | 50 | 6 000 |
Week 1 | South | Product D | Blue | 100 | 1 000 |
Week 1 | South | Product E | Blue | 200 | 1 000 |
Week 1 | East | Product A | Yellow | 100 | 1 500 |
Week 1 | East | Product B | Yellow | 150 | 1 500 |
Week 1 | East | Product C | Yellow | 10 | 1 500 |
Regards
Andreas
Hi @rorand
To make values change with slicer, please consider use a measure instead of calculated column.
would you like the following?
Measure = CALCULATE ( SUM ( Sheet2[My revenue] ), FILTER ( ALLSELECTED ( Sheet2 ), [Item group] = MAX ( [Item group] ) && [Customer] = MAX ( [Customer] ) ) )
If you select week1, the measure will calculate the total revenue per Customer for different Item group in week1.
If your requirement is not like this, please show an example for better analysis.
Best Regards
Maggie
Hello again.
Thank you for your replies @Ashish_Mathur and @v-juanli-msft .
I tried your suggestions but still could'nt find a way to solve my problem.
Therefore I have'nt looked at this for some time, but now I am trying to fix this again.
So, I will try to make you better understand what I am trying to fix.
Data
Here is a Dropbox-link to an Excel-sheet with an edited sample of my data.
A short explanation of the two right-most colums in the sheet:
Revenue: shows the revenue for the product at the store for that specific date
Total revenue for Product Group in store: shows the revenue for the whole product group at the store for that specific date (not only my products, but also my competitors products that are in the same product group.
What I am trying to calculate:
1. The sum of Total revenue for Product Group in store
2. My revenues %-share of Total revenue for Product Group in store
So the problem that I am having trouble with is that I have a lot of products that are in the same category, and therefore the Total revenue for Product Group in store will repeat itself per item that are in the same product group for one store and one specific day.
It is easier to understand if you take a look at my Dropbox-file.
In my original data I also have postal-codes, region and store-chain for each customer, so I also want to be able to aggregate the two measures up and down as i like with filters on postal codes, region, store-chain, product group an product.
To say it easy I want theese measures to work for a single product for each store, and also for all the different levels - both on the customer- and product-side of my data.
Hope this will make you understand better what my problem is, and can help me find a solution 🙂
Best regards
Andreas
Hi,
On the file that you have shared, please show the exact result which you are expecting.
Hello @Ashish_Mathur and thank for your reply.
I have added a Picture-sheet in the Excel-book showing a couple of the visuals I am trying to get.
Also, HERE is a .pbix-file, but this is for now showing the same as in the picture 🙂
Best regards
Andreas
Hi,
I do not understand your requirement. Share a simple dataset and show the exact expected result in a simple Table format.
Hello again @Ashish_Mathur.
Sorry for my bad explanation and thank you for your patience.
This is what I have and what I am expexting to get, with customer 442 as an example, the two rightmost columns are showing my expected results.
As you can see, it is in the Totals I am having problems:
Red colour = wrong totals
Green colour = correct totals
This is because the total for the Product Group Category is already defined per line on each product.
Date | Customer Name | Product Group | Product Name | Revenue | Total revenue for Product Group in store | My Share | Exact expected result of Total revenue for Product Group in store | Exact expected result of My Share |
17.03.2020 | Customer 442 | Product Group 1 | Product Name 137 | 49 | 5624 | 1 % | 5624 | 1 % |
17.03.2020 | Customer 442 | Product Group 1 | Product Name 140 | 55 | 5624 | 1 % | 5624 | 1 % |
17.03.2020 | Customer 442 | Product Group 1 | Product Name 176 | 16 | 5624 | 0 % | 5624 | 0 % |
17.03.2020 | Customer 442 | Product Group 1 | Product Name 178 | 34 | 5624 | 1 % | 5624 | 1 % |
17.03.2020 | Customer 442 | Product Group 1 | Product Name 205 | 21 | 5624 | 0 % | 5624 | 0 % |
17.03.2020 | Customer 442 | Product Group 1 | Product Name 6 | 21 | 5624 | 0 % | 5624 | 0 % |
17.03.2020 | Customer 442 | Product Group 1 | Total | 195 | 33743 | 1 % | 5624 | 3 % |
17.03.2020 | Customer 442 | Product Group 2 | Product Name 4 | 13800 | 22914 | 60 % | 22914 | 60 % |
17.03.2020 | Customer 442 | Product Group 2 | Product Name 80 | 167 | 22914 | 1 % | 22914 | 1 % |
17.03.2020 | Customer 442 | Product Group 2 | Product Name 82 | 229 | 22914 | 1 % | 22914 | 1 % |
17.03.2020 | Customer 442 | Product Group 2 | Product Name 96 | 22 | 22914 | 0 % | 22914 | 0 % |
17.03.2020 | Customer 442 | Product Group 2 | Product Name 99 | 239 | 22914 | 1 % | 22914 | 1 % |
17.03.2020 | Customer 442 | Product Group 2 | Total | 14457 | 114570 | 13 % | 22914 | 63 % |
17.03.2020 | Customer 442 | Product Group 5 | Product Name 142 | 38 | 6565 | 1 % | 6565 | 1 % |
17.03.2020 | Customer 442 | Product Group 5 | Product Name 146 | 20 | 6565 | 0 % | 6565 | 0 % |
17.03.2020 | Customer 442 | Product Group 5 | Product Name 182 | 19 | 6565 | 0 % | 6565 | 0 % |
17.03.2020 | Customer 442 | Product Group 5 | Total | 77 | 19695 | 0 % | 6565 | 1 % |
17.03.2020 | Customer 442 | Total | 14730 | 168007 | 9 % | 35103 | 42 % | |
17.03.2020 | Total | 14730 | 168007 | 9 % | 35103 | 42 % |
Regards
Andreas
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
I am not clear about your question but try these measures
My total revenue = SUM(Data[My revenue])
Item Group total revenue = MIN(Data[Revenue total Item group in store])
Anybody have a solution for this?
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |