Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Further calculation on chart filtered product

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
ProductIDProductNameProductFamilyListPrice
P0001BlueF1500
P0002RedF1400
P0003WhiteF2400
P0004GreenF2300
P0005YellowF3300

 

Opportunity table
OpportunityIDOpportunityNameRegionAccountStatusDateQuoteIDProductIDProductQuantityProductDiscount
OID_001Opp-001EMEAMicrosoftInitiation1-MayQID_001P00011000.00%
OID_002Opp-002EMEAMicrosoftInitiation15-MayQID_002P000150100.00%
OID_002Opp-002EMEAMicrosoftInitiation15-MayQID_002P000240100.00%
OID_003Opp-003APACTeslaIn progress1-JunQID_003P00021025.00%
OID_003Opp-003APACTeslaIn progress1-JunQID_003P00031010.00%
OID_003Opp-003APACTeslaIn progress1-JunQID_003P000410.00%
OID_004Opp-004USAmazonCompleted20-JunQID_004P000180100.00%
OID_004Opp-004USAmazonCompleted20-JunQID_004P000250100.00%
OID_004Opp-004USAmazonCompleted20-JunQID_004P00031020.00%
OID_004Opp-004USAmazonCompleted20-JunQID_004P000450.00%
OID_004Opp-004USAmazonCompleted20-JunQID_004P000510.00%

 

0 REPLIES 0

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors