Hi,
I have the following problem. I have the blow tables/querries connected via the ProductCode. I am designing a report which gives me the total quantity of the sold products (in a TOPN bar format) and a table which displays the Opportunity, the status, the total amount before discount, the aggregated discount and the value of the opportunity after discount. In the same report I also have other filters based on region, industry, status etc. along with a multi-card which summarizes the amount of the discounted opportunites along with their count.
So far so good. However, in the bar chart with the product/quantity, when I select one of the products, the Opportunity table and the multi-card are also filtered but, of course, they show the corresponding amount and count of the opportunities which contain that product. For example, if I click on Product P0001, the table shows Opportunities OID_001, OID_002 and OID_004 (with their corresponding total and discounted amount while the multi-card is showing the total discounted amount and count 3 (because there are only 3 Opps containing product P0001.
I would like to create a dynamic table or measures (to put in a multi-card) to show only the quantity of the selected (clicked in the bar chart) product, the total price (list price x quantity), the average discount across all Opportunities, and the final sold price (total price x discount) AND the whole thing to still be filtered by the page level filters. For example, if no page filter is applied then the bar chart will list all 5 products as bars with the total quantity at their end (e.g P0001 = 230, P0002 = 100 etc.). If region will be EMEA then the bar chart will display only P0001 = 150 and P0002 = 40). If I click on P0001 then the new table/multi-card should show something like this: Quantity = 150, Total price = 75.000 (150 pcs x 500 list price), Average discount = 50%, Sold price = 37.500.
If no product is selected then it would be great if this table or multicard will show either the best selling product with the above parameters (in which case I'll have to think of a way to do a dynamic label) or maybe even be covered with a message box saying something like "Please click on a Product for detailed analysis"
Thanks for helping,
Product table
| ProductID | ProductName | ProductFamily | ListPrice |
| P0001 | Blue | F1 | 500 |
| P0002 | Red | F1 | 400 |
| P0003 | White | F2 | 400 |
| P0004 | Green | F2 | 300 |
| P0005 | Yellow | F3 | 300 |
Opportunity table
| OpportunityID | OpportunityName | Region | Account | Status | Date | QuoteID | ProductID | ProductQuantity | ProductDiscount |
| OID_001 | Opp-001 | EMEA | Microsoft | Initiation | 1-May | QID_001 | P0001 | 100 | 0.00% |
| OID_002 | Opp-002 | EMEA | Microsoft | Initiation | 15-May | QID_002 | P0001 | 50 | 100.00% |
| OID_002 | Opp-002 | EMEA | Microsoft | Initiation | 15-May | QID_002 | P0002 | 40 | 100.00% |
| OID_003 | Opp-003 | APAC | Tesla | In progress | 1-Jun | QID_003 | P0002 | 10 | 25.00% |
| OID_003 | Opp-003 | APAC | Tesla | In progress | 1-Jun | QID_003 | P0003 | 10 | 10.00% |
| OID_003 | Opp-003 | APAC | Tesla | In progress | 1-Jun | QID_003 | P0004 | 1 | 0.00% |
| OID_004 | Opp-004 | US | Amazon | Completed | 20-Jun | QID_004 | P0001 | 80 | 100.00% |
| OID_004 | Opp-004 | US | Amazon | Completed | 20-Jun | QID_004 | P0002 | 50 | 100.00% |
| OID_004 | Opp-004 | US | Amazon | Completed | 20-Jun | QID_004 | P0003 | 10 | 20.00% |
| OID_004 | Opp-004 | US | Amazon | Completed | 20-Jun | QID_004 | P0004 | 5 | 0.00% |
| OID_004 | Opp-004 | US | Amazon | Completed | 20-Jun | QID_004 | P0005 | 1 | 0.00% |
