Other Products then selected in the filter

[MustafaHaider]

ClientGroup	ClientName	ProductId	ProductGroup	Product_Name	OrderDate	Quantity	SalesAmount
Agency	ABC Ltd	15	A	AbC	22/12/2022	1	23
Agency	ABC Ltd	15	A	AbC	22/12/2022	3	23
Agency	ABC Ltd	15	A	AbC	24/10/2023	1	23
Agency	ABC Ltd	15	A	AbC	19/12/2023	1	23
Agency	ABC Ltd	15	A	AbC	19/12/2023	3	23
Agency	ABC Ltd	27	B	DEF	05/12/2022	1	50
Agency	ABC Ltd	27	B	DEF	05/12/2022	2	50
Agency	ABC Ltd	27	B	DEF	24/10/2023	1	50
Agency	ABC Ltd	27	B	DEF	29/11/2023	1	50
Agency	ABC Ltd	27	B	DEF	29/11/2023	2	50
Agency	ABC Ltd	27	B	DEF	19/02/2024	2	50

I have got this sample data set

I have got filter on Product Group, if user has selected Product Group A I would like to show this in table visual

This is for selected product group total value for a product if multiple products are there those will come in the table.

ClientGroup	ClientName	ProductName	Amount
Agency	ABC Ltd	AbC	115

In second visual i would like to see other than selected product group

ClientGroup	ClientName	ProductName	Amount
Agency	ABC Ltd	DEF	300

I am struggling to get the second visual. could you please help.

[v-cgao-msft · Accepted solution]

Hi @MustafaHaider ,

Consider creating another separate table:

And then please create a new measure:

Amount = 
VAR __selected_values = ALLSELECTED('Table'[ProductGroup])
VAR __result = CALCULATE(SUM('Table 2'[SalesAmount]), NOT 'Table 2'[ProductGroup] IN __selected_values)
RETURN
__result

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly--How to provide sample data in the Power BI Forum--China Power BI User Group

[v-cgao-msft · Accepted solution]

Hi @MustafaHaider ,

Consider creating another separate table:

And then please create a new measure:

Amount = 
VAR __selected_values = ALLSELECTED('Table'[ProductGroup])
VAR __result = CALCULATE(SUM('Table 2'[SalesAmount]), NOT 'Table 2'[ProductGroup] IN __selected_values)
RETURN
__result

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly--How to provide sample data in the Power BI Forum--China Power BI User Group

[MustafaHaider]

Apologies one more thing to add in this, e.g. 

If  Product Group A has got other products ABC, D, E, F

Client ABC Ltd, Already have got ABC product.  I would like to exclude those product which existing clients already have got in the same product group.  So result in the suggested product should be D, E and F. i.e. Upsell. How can I do that. 

In above solution it will show other product groups than selected. but also shows the products Client already have got, so i would like to exclude those products which client already have got.

[MustafaHaider]

Thank you so much for providing this solution, as I have a got a lot of Products, in Amount Measure, I would like to have TOP 3 only, how can I do that? I was checking in Visual filters it was not working.

[MNedix · Accepted solution]

You can easily do a filtering TopN as in the screenshot below. However, depending on your data, it may return weird results. In that case it would be better to use a TopN measure.

[MNedix]

You can find the pbix here - https://we.tl/t-9kobo7kpcv but I didn't really do more than what I previously stated.

By the way, if this solved your probelm then please mark it as a solution so others can see it.

[MNedix]

How do you want the second visual? What shape/format? I replicated your problem and made it a card.
Since you said you are using the Product Group as a filter, I just created a measure as below:

Remaining Value = 
var _selected = CALCULATE(
    SUM(Sheet1[SalesAmount]),Sheet1[ProductGroup] = SELECTEDVALUE(Sheet1[ProductGroup]))
var _all = CALCULATE(SUM(Sheet1[SalesAmount]),REMOVEFILTERS(Sheet1))
RETURN
_all-_selected

The 1st variable calculates the amount of the selected product (based on the Product Group filter). The 2nd variable is the total amount. The result is simply the difference, shown as a number in a card (see attached).

If it works, please give it a kudos so others can see the solution.

[MustafaHaider]

THanks for your reply, in second visual I want Sales value by product Group Client has bought other than selected. Pie chart should do the job, it will have product groups and sales values.

Pie chart should show in this case product group B, and Value will be 300. 

If there are multiple clients like in data i do have that scenario, can i do top 3 by sales value i.e. only show top 3 product groups by sales value. 

[MustafaHaider]

Please share pbix file so i can see the relationships you have created. that will help to understand. thanks.